VBA to hide rows

Russk68

Active Member
Joined
May 1, 2006
Messages
473
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

There are many potential solutions to your question ...

The easiest one is to use Filter ...

Hope this will help
 

Russk68

Active Member
Joined
May 1, 2006
Messages
473
Hi James
I appreciate that but I'm looking for a VBA solution.

Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,210
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Private Sub Worksheet_Activate()
   Range("A:A").AutoFilter 1, 0
End Sub
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680

ADVERTISEMENT

Hi,

You could test following

Code:
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
 
Joined
Mar 25, 2019
Messages
13
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..

Code:
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!
 
Joined
Mar 25, 2019
Messages
13

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,210
Office Version
  1. 365
Platform
  1. Windows
Just realised that I did not read the OP properly & my code is doing the exact opposite of what was requested :banghead:

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

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
@ 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:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,018
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top