VBA to hide rows

Russk68

Well-known Member
Joined
May 1, 2006
Messages
589
Office Version
  1. 365
Platform
  1. MacOS
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi,

There are many potential solutions to your question ...

The easiest one is to use Filter ...

Hope this will help
 
Upvote 0
Hi James
I appreciate that but I'm looking for a VBA solution.

Thanks!
 
Upvote 0
How about
Code:
Private Sub Worksheet_Activate()
   Range("A:A").AutoFilter 1, 0
End Sub
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
Just realised that I did not read the OP properly & my code is doing the exact opposite of what was requested :oops:

It should be
Code:
Private Sub Worksheet_Activate()
   Range("A:A").AutoFilter 1, "<>0"
End Sub
 
Upvote 0
@ 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:
 
Upvote 0
Hot dog!
Coolest 3 lines of code I ever saw!

Thank you Fluff!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top