Hide rows conditionally in excel table with VBA

Question

I need to be able to hide rows in a table if the first column is blank. I need the macro to work on tables in different sheets so I search for the table name first using listobjects, I have no problem getting the table name. I have seen how to accomplish this with a general range of cells, but not within a Table. Any help is appreciated.

I have a similar macro to unhide rows in the table and it works fine because it simiply loops through all rows in the ListObject variable 'MyTable' and does not have the IF statement.

    HideBlankTableRows()

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim myTable As ListObject
    Dim row As Range

    Set ws = ActiveSheet
    Set myTable = ws.ListObjects(1)

    For Each row In myTable.DataBodyRange
       If row.Columns(1, 1).Value = "" Then     ' Error is caused by this row
           row.Hidden = True
        End If
     Next

    End Sub

Jaycal · Accepted Answer · 2015-09-10 02:41:51Z

2

Each row In myTable.DataBodyRange will actually loop through each cell in the body of the table, which you probably don't want. Since you're only checking the first column in each row, it would be faster to loop through each row in the table using Each row In myTable.DataBodyRange.Rows.

Also, ~~the Range object doesn't have a Columns property, so you'll have to~~ you can use the Cells property and provide the row and column number of the cell you want to reference (row 1, column 1).

The updated code would be as follows:

For Each row In myTable.DataBodyRange.Rows
    If row.Cells(1, 1).Value = "" Then
       row.Hidden = True
    End If
Next

edited Sep 10, 2015 at 2:41

answered Sep 10, 2015 at 1:46

Jaycal

2,0871 gold badge13 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

chris neilsen Over a year ago

Small note, Range does have a Columns property. row.Columns(1).Value will also work here.

Jaycal Over a year ago

right, Columns(1) and not Columns(1,1). Will update just to show Cells as another option

DD CPA-MBA-DBA Over a year ago

This works flawlessly and I really like Chris's approach of using row.Columns(1).value, It looks so simple and makes so much sense, only after seeing the right way to do it. Thanks so much.

DD CPA-MBA-DBA Over a year ago

Since you guys are so great, I would like to add a twist and add one more question. I would like to find the last row in the table with data in the first column (I know how to find the last row when its just a range of cells). and then make the next row visible. essentially Im hiding all blank rows but leaving the next one available. Let me know if I should post this as a new question

DD CPA-MBA-DBA Over a year ago

I found the answer to my additional question so I thought I would share. Just need use activecell.offsett also do a Do Until loop to skip over any hidden rows

chris neilsen · Accepted Answer · 2015-09-10 02:21:40Z

0

In addition to the fix provided by JayCal, you can utilise the ListObject properties to reference the column by name:

For Each rw In myTable.ListColumns("ColumnName").DataBodyRange
    If rw.Value = vbNullString Then
        rw.EntireRow.Hidden = True
    End If
Next

You could also use the ListObject AutoFilter method

myTable.Range.AutoFilter Field:=lo.ListColumns("ColumnName").Index, Criteria1:="<>"

answered Sep 10, 2015 at 2:21

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Collectives™ on Stack Overflow

Hide rows conditionally in excel table with VBA

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related