Delete Blank rows in Table using VBA

[Rudi]

Hi,

I need to delete all blank cells down the A column of an excel table. ALL code I tried failed or does STRANGE things...

Any idea to get this right will be appreciated...TX

I tried and edited and tried...based on these lines of code....
None of the three versions does the job...

PLEASE HELP :)

Code: Select all

Sub ClearBlankRows()
Dim myR As Range, myRow As Long
    'On Error Resume Next
    'ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="="
    For Each myR In Range("Table1[DATE]").SpecialCells(xlCellTypeBlanks)
    myRow = myR.Row
    myR.Rows(myRow).Select
    '.ListObject.ListRows(myRow).Select
    Next myR
    'ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1
    Range("Table1[[#Headers],[DATE]]").Select
    'On Error GoTo 0
End Sub


Sub ClearBlankCellsInColumnNew()
Dim rngBlanks As Excel.Range

With Worksheets("Master").ListObjects("Table1")
    On Error Resume Next
    Set rngBlanks = Intersect(.DataBodyRange, .ListColumns("New").Range).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rngBlanks Is Nothing Then
        rngBlanks.Delete
    End If
End With
End Sub

Sub T()
Dim myC As Range, i As Long
Set myC = Columns(1).SpecialCells(xlCellTypeBlanks)
For i = 1 To myC.Count
myC(i).Select
myC(i).EntireRow.Delete
Next i
End Sub

[Rudi]

Please note... the code block above is a mess with repeated attempts to get the code to work...
Sorry.... :(

[HansV]

Try

Code: Select all

Sub DeleteBlanks()
    With ActiveSheet.ListObjects("Table1")
        .Range.AutoFilter Field:=1, Criteria1:="="
        .DataBodyRange.EntireRow.Delete
        .Range.AutoFilter Field:=1
    End With
End Sub

[Rudi]

Thanks...but the line: .DataBodyRange.EntireRow.Select ends up selecting the entire table rows (even the hidden ones behind the filter).
Any other ideas?

[HansV]

There is no line .DataBodyRange.EntireRow.Select in the code that I posted.
I assume that you mean that entire rows are deleted, not just the cells within the table.
This macro will only delete cells within the table:

Code: Select all

Sub DeleteBlanks2()
    Dim i As Long
    Application.ScreenUpdating = False
    With ActiveSheet.ListObjects("Table1")
        For i = .ListRows.Count To 1 Step -1
            If .ListRows(i).Range.Cells(1) = "" Then
                .ListRows(i).Delete
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

[Rudi]

I still seemed to have a problem with the filter version... Not sure why.
However, your loop version is working GREAT!! Many TX.
Appreciate the code... :)

[djon5020]

Hans -- thanks for that post.

I had been trying a loop delete with no success. Your code worked great for my purpose.


Cheers and have a Merry Christmas

D

[HansV]

Welcome to Eileen's Lounge, and thanks for your kind words.
Merry Christmas to you too.