Tuesday, December 24, 2024

VBA Excel - delete blank rows from Excel sheet:

The following code deletes blank rows from Excel sheet:
Sub pDelBlnkRws()
    Dim lngMaxR As Long
    Dim lngC As Long
    Dim rngStart As Range
    
    On Error GoTo lblError3
    
    Set rngStart = Sheet1.Range("A" & Sheet1.Rows.CountLarge)
    
    For lngC = 1 To 11
        If rngStart.End(xlUp).Row > lngMaxR Then
            lngMaxR = rngStart.End(xlUp).Row
        End If
        Set rngStart = rngStart.Offset(ColumnOffset:=1)
    Next
       
    For lngC = lngMaxR To 1 Step -1
        With Sheet1
            If Application.WorksheetFunction.CountA(.Range("A" & lngC).EntireRow) = 0 Then
                .Range("A" & lngC).EntireRow.Delete
            End If
        End With
    Next
    
lblError3:
If Err.Number <> 0 Then
    MsgBox "Error Number:" & Err.Number & vbCrLf & "Error Description: " & Err.Number
End If
End Sub

No comments:

Post a Comment

Hot Topics