Hide Next row based on Cell Value
 

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.

Hide Next row based on Cell Value
 

Hi,

you could use Excel's AdvancedFilter to show only the rows when there is
data in Column C.

If you haven't used AdvancedFilter (in a long time), look he
http://www.contextures.com/xladvfilter01.html

and concerning your problem, especially he
http://www.contextures.com/xladvfilter02.html#Blank

Of course, you can start the AdvancedFilter via VBA...

Regards,
xlDominik.


VS182501 schrieb:
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.

Hide Next row based on Cell Value
 

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

--
If this post helps click Yes
---------------
Jacob Skaria


"VS182501" wrote:

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.

Hide Next row based on Cell Value
 

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.



Jacob Skaria schrieb:
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