# 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?

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