Hiding rows only when a condition has been met

[ordnance1]

I am trying to come up with something that will look at rows 3 through 2800
and hide any row where the value in column g (of the respective row) was zero.

 

[Rick Rothstein \(MVP - VB\)]

Give this code a try...

Sub HideRowIfZeroInG()
Dim R As Range
Dim LastRow As Long
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
If LastRow > 2800 Then LastRow = 2800
For Each R In Range("G3:G" & CStr(LastRow))
If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True
Next
End Sub

Rick

 

[Patrick C. Simonds]

Nothing happens when I run this code.

Give this code a try...

Sub HideRowIfZeroInG()
Dim R As Range
Dim LastRow As Long
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
If LastRow > 2800 Then LastRow = 2800
For Each R In Range("G3:G" & CStr(LastRow))
If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True
Next
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

Where are you running it from? It was designed to be placed in the code
window for the worksheet you want it to apply to. So, if Sheet3 is the
worksheet you want to hide the rows in, right-click the name tab for that
worksheet, select View Code from the menu that pops up, and copy/paste the
code into the code window that appeared. Then, go to Sheet3 and press
Alt+F8, select HideRowIfZeroInG from the list and click the Run button. If
you would like to keep the code in some different location, then we will
have to incorporate a reference to the sheet name within the code itself.
The following modification to what I posted earlier will allow that...

Sub HideRowIfZeroInG()
Dim R As Range
Dim LastRow As Long
With Worksheets("Sheet3")
LastRow = .Cells(Rows.Count, "G").End(xlUp).Row
If LastRow > 2800 Then LastRow = 2800
For Each R In .Range("G3:G" & CStr(LastRow))
If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True
Next
End With
End Sub

Note: Change the reference to Sheet3 (keep the quote marks) in the With
statement to the actual sheet name you want to hide the rows on.

Rick

 

[Patrick C. Simonds]

Thank you very much. I totally over looked to reference to sheet3

 

[BJ]

Hi Rick

I tried to use the code and couldn't get it to work ... until ...

I either removed the 'And' portion in the following line or changed the
'And' to 'Or':

If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True

Isn't the line as originally written attempting to hide the row if the value
= 0 AND if the value isn't blank? So no rows will ever be hidden?

Just curious and thanks for all your help Rick.

Brett

 

[BJ]

Hi Rick

As a follow-up ... I'd like to be able to unhide these specific rows as
well. I used your original code to do this but it does it line by line - not
very fast. Is there a simpler [quicker] macro that can unhide a set range of
rows? In my case it's row 85-136.

Thanks for your help Rick.

B