Hide rows in table if cell is blank

Hello,

sorry if this has been posted elsewhere, but I've tried all suggestions previously provided here and it didn't work for me. I'm completely new to VBA and would be VERY thankful for your help. Here's my problem:

I'm trying to create a table that adjusts dynamically to how many cells in a row have text in them. In other words, when a cell is left blank, the entire row should not appear in the table.

I've tried the following code:

Sub test()

Dim i As Integer
Dim nrrows As Integer
Dim sheetname As String
Dim column As Integer
Dim startrow As Integer

sheetname = "Test"
column = 1
startrow = 1

For i = 0 To 15
Rows(startrow + i).Select
Selection.EntireRow.Hidden = False
If Cells(startrow + i, column) = "" Then
Rows(startrow + i).Select
Selection.EntireRow.Hidden = True
End If
Next i

End Sub

When I run this code, it hides all rows, even the ones that aren't blank. Any suggestions on why this might happen and how I can make it work?

Thanks so much in advance!

andycr's picture

Re: Hide rows in table if cell is blank

Hi,

I always try to avoid using Range.Select followed by Selection.DoSomething as all kinds of things can go wrong. However, your code works for me so there must be something else wrong on your worksheet.

After testing your code, I streamlined it a bit:

Sub test()

Dim i As Integer
Dim column As Integer
Dim startrow As Integer

column = 1
startrow = 1

For i = 0 To 15
If Cells(startrow + i, column) = "" Then
Rows(startrow + i).EntireRow.Hidden = True
Else
Rows(startrow + i).EntireRow.Hidden = False
End If
Next i

End Sub