VBA to hide rows

[Russk68]

Hi All,

I want to hide a row when the value in column A=0. If there needs to be a limit then it would be A1:A1000 with any number of rows that can be hidden.
I would also like the code to run only when the sheet is opened.

Thank you!

Russ

 

[James006]

Hi,

There are many potential solutions to your question ...

The easiest one is to use Filter ...

Hope this will help

 

[Russk68]

Hi James
I appreciate that but I'm looking for a VBA solution.

Thanks!

 

[Fluff]

How about

Private Sub Worksheet_Activate()
   Range("A:A").AutoFilter 1, 0
End Sub

 

[James006]

Hi,

You could test following

Sub HideZeroRows()
  With Sheet3
   If .AutoFilterMode = False Then .Range("A1").AutoFilter
          .Range("A1").AutoFilter Field:=1, Criteria1:="<>" & "0"
  End With
End Sub

Hope this will help

 

[Garden Utopia Productions]

I've also got a similar use for this, except instead of a cell value =0, I needed it to check for a string value of "Done" .

Fluff - I tried your version and changed 0 to "Done", but it hid my entire sheet.

James006 - I tried yours as well and replaced "0" with "Done" but this also did nothing. Why does your code use .Range and Fluff's does not?

RussK68 - I came across this on the interwebs and it worked for me on a test sheet..

Private Sub Worksheet_Calculate()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For Each c In Range("A1:A" & LastRow)
   If c.Value = "0" Then
        c.EntireRow.Hidden = True
    End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub

Hope one of these options works for you!

 

[Garden Utopia Productions]

I created another test sheet with just numbers in a range and tried your code again - I see what you did there by creating a filter in the top cell. Good to know!

 

[Fluff]

Just realised that I did not read the OP properly & my code is doing the exact opposite of what was requested

It should be

Private Sub Worksheet_Activate()
   Range("A:A").AutoFilter 1, "<>0"
End Sub

 

[James006]

@ Garden Utopia Productions

Just tested the macro posted in message #5 ...

and it does what it is supposed to ...

Luckily ... have nor produced an Utopia !!! :wink:

 

[Russk68]

Hot dog!
Coolest 3 lines of code I ever saw!

Thank you Fluff!