CODEWITHSUNDEEP
excelvba
Sandy Candy
Sandy Candy

Reputation: 43

Clean trim cells from column A and delete entire row with blank cell in column A

I am trying to trim clean column A then filter the blank cells in column A and delete the entire row based on blank cell present in column A.

Main Excel Main Data

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

Desired output:
Desired output

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
Error in code line

Error Code

Upvotes: 1

Views: 96

Answers (1)

Skin
Skin

Reputation: 11262

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.

MyTable

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.

Upvotes: 1

Related Questions