I am very new to vba macro I am trying to create a code that trim clean the column A and then filters the blank cells present in only column A and delete the entire row based on blank cell present in column A

Column A has has few blank cell those blank cells might have invalid space hence first want to clean and trim the entire column A and then filter on column A blank cell and delete the entire row.

Desired output as follows : -

My codes

Sub trimclean()

Dim lRow As Integer, i As Long

With Worksheets("Sandy")

lRow = .Range("A1").End(xlDown).Row

For i = 2 To lRow
        .Cells(i, "A").Value = Trim(.Cells(i, "A").Value)
    Next i

End With

End Sub


Sub DeleteBlanks()

    Dim rDataToProcess As Range

    Set rDataToProcess = ActiveWorkbook.Worksheets("Sandy").Range("A1").End(xlDown).Row.CurrentRegion

    'Field in the below method refers to the column that is being filtered, so the second colum
    rDataToProcess.AutoFilter field:=2, Criteria1:=""
    rDataToProcess.Offset(1).Resize(rDataToProcess.Rows.Count).EntireRow.Delete

    Sheet1.AutoFilterMode = False

End Sub

Problem in my code is

All I need is to trim clean column A and filter on blank cells on column A and then based on blank cell present on column A delete the entire row.

CodePudding user response：

For a start, it looks to be that your "Range" is actually a qualified table. If so, you can refer to the ListObjects in the worksheet and it makes it easier to modify the table.

It loops but at least you can see what it's doing. If you wanted it to delete all rows in a single call then that is possible but too many rows in the table and the deletion would need to be broken out and packetised.

Also, I'm not sure if you want to do it in two steps but I've provided for that here ...

2 Steps

Public Sub TrimCells()
    Dim objTable As ListObject, lngRow As Long, lngColumnToTrim As Long
    
    lngColumnToTrim = 1
        
    Set objTable = GetTable
    TogglePerformance False
    
    With objTable.DataBodyRange
        For lngRow = 1 To .Rows.Count
            .Cells(lngRow, lngColumnToTrim) = Trim(.Cells(lngRow, lngColumnToTrim))
        Next
    End With
    
    TogglePerformance True
End Sub

Public Sub DeleteBlankRows()
    Dim objTable As ListObject, lngRow As Long, lngColumnToCheckForBlank As Long
    
    lngColumnToCheckForBlank = 1
    
    Set objTable = GetTable
    TogglePerformance False
    
    With objTable.DataBodyRange
        For lngRow = .Rows.Count To 1 Step -1
            If Len(.Cells(lngRow, lngColumnToCheckForBlank).Value) = 0 Then
                .Rows(lngRow).Delete xlShiftUp
            End If
        Next
    End With
    
    TogglePerformance True
End Sub

Private Function GetTable() As ListObject
    Set GetTable = ThisWorkbook.Worksheets("Sandy").ListObjects("MyTable")
End Function

Private Sub TogglePerformance(ByVal bOn As Boolean)
    Application.ScreenUpdating = bOn
    Application.EnableEvents = bOn
    
    If bOn Then
        Application.Calculation = xlCalculationAutomatic
    Else
        Application.Calculation = xlCalculationManual
    End If
End Sub

... but if you're happy to do it one one step then that'd be easier I would've though.

1 Step

Public Sub DeleteBlankRows()
    Dim objTable As ListObject, lngRow As Long, lngColumnToCheckForBlank As Long
    
    lngColumnToCheckForBlank = 1
    
    Set objTable = ThisWorkbook.Worksheets("Sandy").ListObjects("MyTable")
    TogglePerformance False
    
    With objTable.DataBodyRange
        For lngRow = .Rows.Count To 1 Step -1
            If Len(Trim(.Cells(lngRow, lngColumnToCheckForBlank).Value)) = 0 Then
                .Rows(lngRow).Delete xlShiftUp
            End If
        Next
    End With
    
    TogglePerformance True
End Sub

Private Sub TogglePerformance(ByVal bOn As Boolean)
    Application.ScreenUpdating = bOn
    Application.EnableEvents = bOn
    
    If bOn Then
        Application.Calculation = xlCalculationAutomatic
    Else
        Application.Calculation = xlCalculationManual
    End If
End Sub

... you just need to make sure you change your Table Name in the code or change it on the sheet itself.

I've also assumed that you want to check in the first column of the table. That made sense given you were checking column A on sheet.

Bottom line, your table could be anywhere and this would still work.