Étiquette : delete

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.

Delete Empty Worksheets VBA Code:  

Sub DeleteEmptyWorksheets()
    Dim ws As Worksheet
    Dim wsCount As Integer
    Dim i As Integer
    Dim lastRow As Long, lastCol As Long
    Dim wsToDelete As Collection
    Dim wsName As String
    Dim response As VbMsgBoxResult
    ' Initialize a collection to store empty worksheet names
    Set wsToDelete = New Collection
    ' Count the total number of worksheets
    wsCount = ThisWorkbook.Worksheets.Count
    ' Prevent deletion if only one worksheet remains
    If wsCount = 1 Then
        MsgBox "Cannot delete the only worksheet in the workbook!", vbExclamation, "Delete Empty Worksheets"
        Exit Sub
    End If
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Find the last used row and column
        lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        ' If no used range is found, the worksheet is empty
        If lastRow = 1 And lastCol = 1 Then
            If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
                ' Store the worksheet name in the collection
                wsToDelete.Add ws.Name
            End If
        End If
    Next ws
    ' Confirm deletion if there are empty worksheets
    If wsToDelete.Count > 0 Then
        wsName = "The following empty worksheets will be deleted:" & vbNewLine & vbNewLine
        For i = 1 To wsToDelete.Count
            wsName = wsName & wsToDelete(i) & vbNewLine
        Next i
        response = MsgBox(wsName & vbNewLine & "Do you want to proceed?", vbYesNo + vbQuestion, "Confirm Deletion")
        If response = vbYes Then
            Application.DisplayAlerts = False
            For i = 1 To wsToDelete.Count
                ThisWorkbook.Worksheets(wsToDelete(i)).Delete
            Next i
            Application.DisplayAlerts = True
            MsgBox "Empty worksheets deleted successfully.", vbInformation, "Delete Empty Worksheets"
        Else
            MsgBox "No worksheets were deleted.", vbInformation, "Delete Empty Worksheets"
        End If
    Else
        MsgBox "No empty worksheets found.", vbInformation, "Delete Empty Worksheets"
    End If
End Sub

Detailed Explanation

This VBA macro scans through all worksheets in the active workbook and deletes those that are empty. Here’s a step-by-step breakdown of the code:

1. Initialize Variables
   • ws: Used to iterate through worksheets.
   • wsCount: Stores the total number of worksheets.
   • i: Loop counter.
   • lastRow and lastCol: Identify the last used row and column.
   • wsToDelete: A collection to store the names of empty worksheets.
   • wsName: Stores worksheet names for confirmation.
   • response: Captures user response in the confirmation message.
2. Check If Only One Worksheet Exists
   • If the workbook has only one worksheet, the macro displays a message and exits because deleting the last worksheet is not allowed.
3. Loop Through All Worksheets
   • The macro examines each worksheet to determine if it is empty.
   • ws.Cells.Find(« * », SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row: Finds the last used row.
   • ws.Cells.Find(« * », SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column: Finds the last used column.
   • If both lastRow and lastCol are 1 and there are no non-empty cells (Application.WorksheetFunction.CountA(ws.Cells) = 0), the worksheet is considered empty.
   • The empty worksheet’s name is stored in the wsToDelete collection.
4. Confirm Deletion with the User
   • If at least one empty worksheet is found, a message box lists the worksheets and asks the user for confirmation before proceeding.
5. Delete Empty Worksheets
   • If the user confirms, the macro:
     • Temporarily disables Application.DisplayAlerts to suppress deletion warnings.
     • Deletes each worksheet in the wsToDelete collection.
     • Re-enables Application.DisplayAlerts.
     • Displays a confirmation message.
6. Handle Cases Where No Empty Worksheets Are Found
   • If no empty worksheets exist, the macro informs the user and exits.

Why This Code Is Effective?

✔ Prevents Deleting the Last Worksheet: Ensures that at least one worksheet remains.
✔ Accurate Detection of Empty Worksheets: Uses .Find and CountA functions to confirm emptiness.
✔ User Confirmation Before Deletion: Prevents accidental deletions.
✔ Batch Deletion Using a Collection: Improves efficiency by first identifying all empty sheets before deletion.
✔ Handles Alerts Properly: Prevents unnecessary warnings during deletion.

VBA Code to Delete Blank Rows

Sub DeleteBlankRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim rng As Range
    ' Set the active worksheet
    Set ws = ActiveSheet
    ' Find the last row with data in the worksheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Loop from the last row to the first row (to avoid skipping rows)
    For r = lastRow To 1 Step -1
        ' Check if the entire row is empty
        If Application.WorksheetFunction.CountA(ws.Rows(r)) = 0 Then
            ws.Rows(r).Delete
        End If
    Next r
    ' Release memory
    Set ws = Nothing
End Sub

Detailed Explanation of the Code

1. Declaring Variables

Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Dim rng As Range

• ws: Stores the reference to the worksheet.
• lastRow: Stores the last used row in the worksheet.
• r: Used as a counter to iterate through rows.
• rng: (Not used in this example but can be useful for range selection).

2. Assign the Active Worksheet

Set ws = ActiveSheet

• This assigns the currently active worksheet to the variable ws, ensuring we operate on the correct sheet.

3. Find the Last Used Row

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

• ws.Rows.Count returns the total number of rows in the sheet (e.g., 1,048,576 for Excel 2007+).
• .End(xlUp) moves upwards from the last row in column A until it finds a non-empty cell.
• This technique effectively finds the last used row in the worksheet.

4. Loop Through Rows (From Bottom to Top)

For r = lastRow To 1 Step -1

• We iterate backward from lastRow to row 1 (Step -1 ensures no row is skipped).
• If we looped from top to bottom, deleting rows would shift the row numbers, causing some blank rows to be missed.

5. Check If the Row is Blank

If Application.WorksheetFunction.CountA(ws.Rows(r)) = 0 Then

• CountA(ws.Rows(r)) counts the number of non-empty cells in the entire row.
• If the result is 0, it means the row is completely empty.

6. Delete the Blank Row

ws.Rows(r).Delete

• Deletes the entire row when it is found to be blank.

7. Release Memory

Set ws = Nothing

• This is good practice to free up memory after executing the macro.

Alternative Approach Using AutoFilter

This method is faster for large datasets.

Sub DeleteBlankRowsWithFilter()
    Dim ws As Worksheet
    Dim rng As Range
    ' Set the worksheet
    Set ws = ActiveSheet
    ' Set the range covering all used rows
    On Error Resume Next
    Set rng = ws.UsedRange
    On Error GoTo 0
    ' Check if the range is valid
    If Not rng Is Nothing Then
        ' Apply filter to find blank rows in column A (change as needed)
        rng.AutoFilter Field:=1, Criteria1:="="  
        ' Delete visible rows after filtering
        On Error Resume Next
        ws.Rows("2:" & ws.Rows.Count).SpecialCells(xlCellTypeVisible).Delete
        On Error GoTo 0    
        ' Turn off filter
        ws.AutoFilterMode = False
    End If
    ' Release memory
    Set ws = Nothing
End Sub

Advantages of AutoFilter Method

Faster on large datasets
Avoids looping through each row
Works efficiently with large spreadsheets

Conclusion

• For small datasets, the first method (looping through rows) works well.
• For large datasets, the AutoFilter method is much faster and more efficient.