Delete Rows And Columns
It is frequently necessary to delete rows and columns in a worksheet. Rows use numeric designations which lend themselves to computational solutions for choosing the row numbers to be deleted. Columns, however, use an Alpha designation for some of the commands, so this example shows how to delete columns using numeric identification.
The program code below shows multiple ways to delete the same rows and columns.
Program Code
Option Explicit
Public Sub DeleteRowsAndColumns()
Dim lngStartingRow As Long
Dim lngEndingRow As Long
Dim lngStartingColumn As Long
Dim lngEndingColumn As Long
lngStartingRow = 6
lngEndingRow = 9
lngStartingColumn = 10
lngEndingColumn = 16
' *****************************************************
' Delete Columns 10 - 16 By Using Alpha Designation
' *****************************************************
Columns("J:P").Delete
' *****************************************************
' Delete The Same Columns Using Numeric Designations
' *****************************************************
Range(Cells(1, 10), Cells(1, 16)).EntireColumn.Delete
' *****************************************************
' Delete The Same Columns Using Numeric Variables
' *****************************************************
Range(Cells(1, lngStartingColumn), Cells(1, lngEndingColumn)).EntireColumn.Delete
' *****************************************************
' Delete Rows 6 - 9 Using Numeric Designations
' *****************************************************
Rows("6:9").Delete
' *****************************************************
' Delete The Same Rows Using Numeric Variables
' *****************************************************
Rows(lngStartingRow & ":" & lngEndingRow).Delete
' *****************************************************
' Delete A Single Row
' *****************************************************
Rows(1000).EntireRow.Delete
End Sub
Program Code To Delete All Rows Past Last Data Row
Option Explicit
' ****************************************************************
' Reset Row Range To Actual Data
' Delete All Rows Past Last Data Row
' ****************************************************************
Public Sub SetActualRange()
Dim lngLastDataRow As Long
Dim lngLastExcelRow As Long
Dim rngRangeToDelete As Range
lngLastDataRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
lngLastExcelRow = ActiveSheet.Cells(Rows.Count, "A").Row
' **********************************************************************************
' The Following Line Can Be Used to Replace The Last Two Lines of Code
' **********************************************************************************
' Range(Cells(lngLastDataRow + 1, 1), Cells(lngLastExcelRow, 5)).EntireRow.Delete
' **********************************************************************************
Set rngRangeToDelete = Range(Cells(lngLastDataRow + 1, 1), Cells(lngLastExcelRow, 5)).EntireRow
rngRangeToDelete.Delete
End Sub
' **********************************************************************************
' Or You Can Use This Method To Delete Both Columns and Rows
' **********************************************************************************
Sub AlternativeMethodToDelete()
Dim LastRow As Long
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
'~~> Delete Extra Rows
Sheets("Sheet1").Rows(LastRow & ":" & Rows.Count).Delete
'~~> Delete Extra Columns just to be safe
Sheets("Sheet1").Columns("F:" & Split(Cells(, Columns.Count).Address, "$")(1)).Delete
End Sub