Macro to Delete blank rows in a Table

[jimmykup]

Hi all,

I recently found this macro on these forums for deleting rows where all of the cells are empty.

Sub RemoveEmptyRows()
On Error Resume Next
    With Range("B300:B1000")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

Unfortunately my efforts to get this to work only in a Table have failed. Here is my modified code that isn't working for me.

Sub a_Remove_Empty_Rows()
On Error Resume Next
    With Range("ProjectReport")
        .Value = .Value
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

Any help would be greatly appreciated!

 

[patel45]

the correct code is

Sub RemoveEmptyRows()
  With Range("B300:B1000")
     .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End With
End Sub

but eliminates rows with blank cells in column B, otherwise you have to extend the range

 

[jimmykup]

Thanks for the reply.

I'm only interested in the macro deleting empty rows in a named table. The Range("B300:B1000") in my original code was just what I got from another thread. I'm trying to change it to only effect the entirety of a named table.

 

[patel45]

it's not clear, do you want eliminate rows or cells ?

.SpecialCells(xlCellTypeBlanks).EntireRow.Delete eliminates the ENTIRE row

if you want eliminate cells

Sub RemoveEmptyCells() 
  With Range("B300:B1000").SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)   
End With 
End Sub

 

[Rick Rothstein]

Unfortunately my efforts to get this to work only in a Table have failed. Here is my modified code that isn't working for me.

Sub a_Remove_Empty_Rows()
On Error Resume Next
    With Range("ProjectReport")
        .Value = .Value
        Intersect(.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

Any help would be greatly appreciated!

Try changing the above to this (untested)...

Sub a_Remove_Empty_Rows()
On Error Resume Next
    With Range("ProjectReport")
        .Value = .Value
        Intersect(.Cells, .SpecialCells(xlCellTypeBlanks).EntireRow).Delete
    End With
End Sub

 

[iwilli]

Okay this is the blind leading the blind but I think you need to reference that you are referencing a table. That is redundant I know. Something like this. Anyone want to tell me I'm an idiot I won't disagree. This code isn't tested.

Sub RemoveEmptyRows()
  With Range(tableName & "[ProjectReport]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End With

 

[jimmykup]

it's not clear, do you want eliminate rows or cells ?

I want to delete entire rows that are entirely blank. But this has to work for a table that specifically target.

Try changing the above to this (untested)...

Sub a_Remove_Empty_Rows()
On Error Resume Next
    With Range("ProjectReport")
        .Value = .Value
        Intersect(.Cells, .SpecialCells(xlCellTypeBlanks).EntireRow).Delete
    End With
End Sub

Unfortunately, this deleted just about every row in my table. Rows with all empty cells and rows with filled cells.

 

[patel45]

can you show an example before and after ?

 

[legendary_popsicle]

Try this:

Sub Delete_Blank_Rows()


ActiveSheet.AutoFilterMode = False

LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
LastColumn = Replace(Cells(1, ActiveSheet.Range("XFD1").End(xlToLeft).Column).Address(False, False), "1", "")

With ActiveSheet.Range("A1:" & LastColumn & LastRow)
.AutoFilter Field:=1, Criteria1:="", Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

ActiveSheet.AutoFilterMode = False


End Sub

 

[TayoMayo]

Hi all,
I am extremely new to VBA, and am trying to program a macro that will delete an entire row based on a range of cells in that row. For example, I need to delete the entire row if cells A3, B3, and C3 are all blank. The code I have pieced together is below. The issue arises on the bold line. As best I can tell, the issue arises with the .Range() part of the line. I get an error that says there is a 'type mismatch' . I'm not entirely sure what that means or how to go about fixing it. Any and all help is greatly appreciated.

Sub DeletingBlankRows()
'Deletes the entire row if certain cells are completelly blank

Worksheets("Sheet1").Activate
Range("A1").Select
Range("A1").Activate
Dim deleter As Long
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        
        For deleter = Selection.Rows.Count To 65 Step 1
        
            If ActiveCell.Offset(1, 0) = ActiveCell.Offset(0, 0) Then
            
                ActiveCell.Offset(1, 0).Select
                Selection.Activate
                
                    [B]If ActiveCell.Offset(0, 3).Range("A1:D3") = "" Then[/B]
                    
                        Selection.Rows(deleter).EntireRow.Delete
                    
                    Else
                    
                        ActiveCell.Offset(1, 0).Select
                        Selection.Activate
                
                    End If
                    
            Else
            
                ActiveCell.Offset(1, 0).Select
                Selection.Activate
                
            End If
            
        Next deleter
        
    End With
        
End Sub