Excel – hide rows in Excel 2010 using VBA if a cell value is blank

microsoft excelvba

I can get the VBA to work just fine to do this, but my spreadsheet is 20MB without data. What I want to do is find the first time a cell is blank and then hide every row at once from that spot until the end. Here is my code that works:

Dim rl As Range
For Each rl In Sheets("Results").Range("$A$4:$A$800")
  If rl.Value = "" Then
      r1.EntireRow.Hidden = True
  Else: rl.EntireRow.Hidden = False
  End If
Next rl

So in this example, if r1.value = "", I want to hide from that row all the way until row 800. Any advice?

Best Answer

For making it faster try this:

Sub Test()
  Dim i As Long
  For i = 4 To 800
    If Sheets("Results").Cells(i, 1).Value = "" Then
      Rows(i & ":" & Rows.Count).EntireRow.Hidden = True
      Rows("1:" & i - 1).EntireRow.Hidden = False
      Exit Sub
    End If
  Next
End Sub

it checks for the first cell in row A without text and hides all rows below it (includes the cell found) and makes all cells visible over it (excluding the found cell)
this will also accept cells as "no text" with formulas having a "" as result

for only hide starting with the real empty cell change

 If Sheets("Results").Cells(i, 1).Value = "" Then

to

If isempty(Sheets("Results").Cells(i, 1).Value) Then

also when only hiding till row 800 change

Rows(i & ":" & Rows.Count).EntireRow.Hidden = True

to

Rows(i & ":800").EntireRow.Hidden = True