none
Excel VBA Code to delete Empty ListObject Row RRS feed

  • Question

  • To get best data model to be use with Pivot Table it is important to erase all Blank Row (row with no data) from a Table.

    So I wrote VBA Code to automate deletion of Blank Row process.

    The code below will check every fields on a specific row of a ListObject (a table in Excel). If every fields (which synonymous with column in Excel) in current row (lets say row_X) has no record or no data then current row will be deleted. Here is the code :

    Sub DeleteBlankRow()
        Dim table As ListObject
        Dim data As Range
        Dim cR, r As Range
        Dim dataFound As Long
        
        Set table = Range("Table1").ListObject
        Set data = table.DataBodyRange
        Set cR = data.rows
        
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        Dim timeCount As Double
        timeCount = Timer
        
        For Each r In cR
           If WorksheetFunction.CountA(r) = 0 Then
                r.Delete Shift:=xlShiftUp
                dataFound = dataFound + 1
           End If
        Next r
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        
        Set table = Nothing
        Set data = Nothing
        Set cR = Nothing
        
        timeCount = Timer - timeCount
        MsgBox "Execution time : " & timeCount & vbNewLine _
        & " Data Found : " & dataFound, vbInformation
    End Sub

    The code work perfect for small data. But when I try it with 50,000 rows of data it takes about 1 minute (67,34 seconds) for the code to finish.

    I'm dealing with huge data on my company.....so I am seek for a better-faster code.

    Could someone show me another version of code (if exists without using super slow For Each Looping)...?

    If anybody could give the VBA Code and C# Code (since I am currently using VSTO as well) I'll be really appreciate it.

    Thanks before, Jesus Bless You....!


    • Edited by nomen86 Wednesday, October 28, 2015 1:35 AM
    Wednesday, October 28, 2015 1:33 AM

Answers

  • To get best data model to be use with Pivot Table it is important to erase all Blank Row (row with no data) from a Table.

    So I wrote VBA Code to automate deletion of Blank Row process.


    Don't delete the rows, use an Autofilter and hide the blanks rows (use a CountA formula in a helper column).

    Andreas.

    Wednesday, October 28, 2015 11:56 AM
  • Re:  deleting rows

    General comment...
    1.  When deleting items from a collection, start at the bottom (last item) and work back to item 1
    2.  It is usually faster to delete the entire worksheet row instead of a portion of a row.
    3.  It is much faster to just clear the rows and then sort the data.
         An auto filled helper column with index numbers can be used to maintain data sequence.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Wednesday, October 28, 2015 2:26 PM

All replies

  • It looks like the pivot table must be getting the data from and excel table (Table1).

    Are you manually pasting info onto the table- as if you were using a table query I don't think you wouldn't have this problem.

    If so you must be clearing the table rather han deleteing the table rows (lots of "I think!")

    If above is true- prior to pasting new info you could delete the rows in the table- manually or in VB

    David

    Sub Clear_Table()
    
    Application.DisplayAlerts = False
    Range("Table1").Select
        Selection.Delete
        Application.DisplayAlerts = True
    end sub
                   

    • Proposed as answer by David_1234 Tuesday, November 3, 2015 9:36 AM
    Wednesday, October 28, 2015 10:55 AM
  • To get best data model to be use with Pivot Table it is important to erase all Blank Row (row with no data) from a Table.

    So I wrote VBA Code to automate deletion of Blank Row process.


    Don't delete the rows, use an Autofilter and hide the blanks rows (use a CountA formula in a helper column).

    Andreas.

    Wednesday, October 28, 2015 11:56 AM
  • Re:  deleting rows

    General comment...
    1.  When deleting items from a collection, start at the bottom (last item) and work back to item 1
    2.  It is usually faster to delete the entire worksheet row instead of a portion of a row.
    3.  It is much faster to just clear the rows and then sort the data.
         An auto filled helper column with index numbers can be used to maintain data sequence.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Wednesday, October 28, 2015 2:26 PM