MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Barrie...I'm running with the VBA suggestion, but have small problem


Posted by JC on November 07, 2001 9:45 AM

Sorry I'm really not very experienced with VBA...only one way to change that!

I've run with your code as follows:

Sub HideRows()
For Each cell In Range("BV7:BV129")
Select Case cell.Value
Case Is = 0
cell.EntireRow.Hidden = True
Case Is = ""
cell.EntireRow.Hidden = True
End Select
Next cell
End Sub

This works great! The only problem is I want to reference an additional column (BW)...in short, I want both columns to be equal to zero, or blank (budget and actual)....

Thanks again for your help...I'm sure this is pretty simple, but like I said...I'm a rookie!


Posted by Barrie Davidson on November 07, 2001 9:53 AM

JC, try this instead.

Sub HideRows()
Application.ScreenUpdating = False
For Each cell In Range("BV7:BV129")
Select Case cell.Value + cell.Offset(0, 1).Value
Case Is = 0
cell.EntireRow.Hidden = True
End Select
Next cell
Application.ScreenUpdating = True
End Sub

Regards,
BarrieBarrie Davidson

Posted by JC on November 07, 2001 10:04 AM

GREAT!! That works perfect....now, any idea how I can understand a little more about what the code is doing? I swear this is the last question for today!


Posted by Barrie Davidson on November 07, 2001 10:14 AM

Code with comments included


JC, here's the code again and I have included comment lines which should help you in understanding what I did.

Sub HideRows()

'Turn off the screen refresh (makes it faster)
Application.ScreenUpdating = False

'Loop through the cells in your range BV7:BV129
For Each cell In Range("BV7:BV129")

'Add the two cells together (cell in column BV
'and the cell in column BW
Select Case cell.Value + cell.Offset(0, 1).Value

'If the cells add to zero, hide the entire row
Case Is = 0
cell.EntireRow.Hidden = True
End Select

'Go to the next cell in the range
Next cell

'Turn the screen refresh back on
Application.ScreenUpdating = True
End Sub

Regards,
BarrieBarrie Davidson