| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| hide row based on cell value | Excel Worksheet Functions | |||
| Hide row based on cell value | Excel Worksheet Functions | |||
| Hide Rows based on value of a cell | Excel Programming | |||
| Is there a way to HIDE a row based on a value of a cell ? | Excel Discussion (Misc queries) | |||
| Hide Row Based on cell value | Excel Programming | |||
Linear Mode
