Hide Next row based on Cell Value

V

VS182501

Can anyone please help?

I am currently repeating the following VBA code several hunderd times and
change the Range and Rows values manually.

Application.ScreenUpdating = False
If Range("C5").Value = "" Then
Rows("6:6").EntireRow.Hidden = True
ElseIf Range("C5").Value <> "" Then
Rows("6:6").EntireRow.Hidden = False
End If

Application.ScreenUpdating = False
If Range("C6").Value = "" Then
Rows("7:7").EntireRow.Hidden = True
ElseIf Range("C6").Value <> "" Then
Rows("7:7").EntireRow.Hidden = False
End If

Application.ScreenUpdating = False
If Range("C7").Value = "" Then
Rows("8:8").EntireRow.Hidden = True
ElseIf Range("C7").Value <> "" Then
Rows("8:8").EntireRow.Hidden = False
End If

Application.ScreenUpdating = False
If Range("C8").Value = "" Then
Rows("9:9").EntireRow.Hidden = True
ElseIf Range("C8").Value <> "" Then
Rows("9:9").EntireRow.Hidden = False
End If

Application.ScreenUpdating = False
If Range("C9").Value = "" Then
Rows("10:10").EntireRow.Hidden = True
ElseIf Range("C9").Value <> "" Then
Rows("10:10").EntireRow.Hidden = False
End If


as you can imagine this is very labor intensive.

I believe that there should be an easier way of doing this, can you please
help?

Thanks in advance.
 
J

Jacob Skaria

Use a loop.The below works from 5 to 100

Dim lngRow As Long

Application.ScreenUpdating = False
For lngRow = 5 To 100
If Range("C" & lngRow).Value = "" Then
Rows(lngRow + 1).Hidden = True
Else
Rows(lngRow + 1).Hidden = False
End If
Next
Application.ScreenUpdating = False
 
D

Dominik Petri

If you want VBA instead of AdvancedFilter, no loop necessary:

With Range("C5:C100")
..Rows.Hidden=False ' Show all
..SpecialCells(xlCellTypeBlanks).EntireRow.Hidden=true ' hide rows when
cell is blank
End With


Regards,
Dominik.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top