Delete RowsColumns in Excel with VBA

VBA Code: Delete Rows and Columns in Excel

Sub DeleteRowsAndColumns()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name as needed
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Find the last column with data
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Delete rows where the first column (A) is empty
    For i = lastRow To 1 Step -1 ' Loop from last row to first (avoids shifting issues)
        If IsEmpty(ws.Cells(i, 1)) Then
            ws.Rows(i).Delete
        End If
    Next i
    ' Delete columns where the first row is empty
    For j = lastCol To 1 Step -1 ' Loop from last column to first (avoids shifting issues)
        If IsEmpty(ws.Cells(1, j)) Then
            ws.Columns(j).Delete
        End If
    Next j
    ' Clean up
    Set ws = Nothing
End Sub

Detailed Explanation

1. Defining the Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

• We define the worksheet where the operation will take place.
• You can replace « Sheet1 » with the actual name of your sheet.

2. Finding the Last Row with Data

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

• ws.Rows.Count returns the total number of rows (typically 1,048,576 in modern Excel).
• End(xlUp) moves upwards from the last row in column A to find the last non-empty cell.
• This helps us determine where the data stops.

3. Finding the Last Column with Data

lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

• ws.Columns.Count returns the total number of columns (typically 16,384 in Excel).
• End(xlToLeft) moves leftward from the last column in row 1 to find the last non-empty cell.
• This helps us determine where the data stops horizontally.

4. Deleting Rows Where Column A is Empty

For i = lastRow To 1 Step -1
    If IsEmpty(ws.Cells(i, 1)) Then
        ws.Rows(i).Delete
    End If
Next i

• The loop starts from the last row and moves upwards (Step -1).
• IsEmpty(ws.Cells(i, 1)) checks if the cell in column A is empty.
• If the condition is met, the entire row is deleted.
• The loop moves in reverse order to avoid shifting issues when deleting rows.

5. Deleting Columns Where Row 1 is Empty

For j = lastCol To 1 Step -1
    If IsEmpty(ws.Cells(1, j)) Then
        ws.Columns(j).Delete
    End If
Next j

• The loop starts from the last column and moves leftwards.
• IsEmpty(ws.Cells(1, j)) checks if the cell in row 1 is empty.
• If true, the entire column is deleted.
• The reverse loop prevents errors caused by column shifting.

6. Cleaning Up

Set ws = Nothing

• This releases the worksheet object from memory to optimize performance.

Key Features

Deletes empty rows based on column A.
Deletes empty columns based on row 1.
Uses reverse loops to avoid shifting issues.
Works dynamically by detecting last used row/column.