Removing blank entries from table with VBA

user_16493566 0
Viewed 243

I have a table of inputs that I run calculations on. Having empty rows in that table affects the calculations. Right now I have a macro that sorts the table everytime there's any changes made, because of which all the blank rows collect at the bottom of the table. So I just have to resize the table manually before every calculation.

You can see an empty row at the bottom of the table here. I don't know how to go about removing it such that the table ends with last non empty entry

enter image description here

But I want to know if there's a way to snap the table grid back to the last non empty row using VBA and remove the empty rows from the table. Thanks!

vba excel
3 Answers

Here is the VBA function that would delete the empty rows

Public Sub DeleteBlankRows()
    Dim LastRowIndex As Integer
    Dim RowIndex As Integer
    Dim UsedRng As Range
 
    Set UsedRng = ActiveSheet.UsedRange
    LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
    Application.ScreenUpdating = False
 
    For RowIndex = LastRowIndex To 1 Step -1
        If Application.CountA(Rows(RowIndex)) = 0 Then
            Rows(RowIndex).Delete
        End If
    Next RowIndex
 
    Application.ScreenUpdating = True
End Sub

Here is sample

enter image description here

And this is how it looks after enter image description here

user_7168130

Delete Blank Rows in an Excel Table

  • No need to sort the table if you don't want to.
Option Explicit

Sub DeleteTableBlankRows()
    
    With Sheet1.ListObjects(1).DataBodyRange
        
        Dim cCount As Long: cCount = .Columns.Count
        
        Dim drg As Range ' Delete Range
        Dim rrg As Range ' Row Range
        
        For Each rrg In .Rows
            If Application.CountBlank(rrg) = cCount Then
                If drg Is Nothing Then
                    Set drg = rrg
                Else
                    Set drg = Union(drg, rrg)
                End If
            End If
        Next rrg
        
        If Not drg Is Nothing Then
            drg.Delete
        End If
    
    End With
            
End Sub
user_98140690

Please, use the next Sub. It will delete all table empty rows after filtering. I mean the last (empty) ones:

Private Sub deleteTableEmptyRows(Optional TblD As Range) 'calculate table last empty rows
   Dim lastShER As Long, lastShTblR As Long, lastTblER As Long, lastTblR As Long
   
   If TblD Is Nothing Then Set TblD = ActiveSheet.ListObjects(1).DataBodyRange
   lastShER = TblD.cells(TblD.rows.count, 1).End(xlUp).row + 1 'last empty row on the sheet (no needed, only to demonstrate how to calculate)
     lastTblER = lastShER - (TblD.row - 1)                     'last empty row on the table DataBodyRange
   lastShTblR = TblD.rows.count + TblD.row                     'last table row on the sheet
     lastTblR = lastShTblR - TblD.row                          'last table row
   If lastTblER > 1 Then TblD.rows(lastTblER & ":" & lastTblR).Select 'please, change Select with Delete, only after checking that selected range is as you need
End Sub

It can be called in this way:

Sub testDeleteTableEmptyRows()
    Dim TblD As Range
     Set TblD = ActiveSheet.ListObjects(1).DataBodyRange 'use here the table name (or index)
     deleteTableEmptyRows TblD
End Sub
user_22333080
Related