Hide rows in range when value in COL A = 0

JamesGilchrist

New Member
Joined
Apr 12, 2011
Messages
13
Hi All,

Need some code to do the following.

For a range of rows from Row 10 to 50000
Hide any row where COL A has a value = 0

Many thanks....sorry for such a numpty question.

James
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,977
Office Version
2013
Platform
Windows
If the 0 is NOT the result of a formula, use

Code:
Sub MM1()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace 0, "#N/A", xlWhole, , False, , False, False
    Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Hidden = True
  End With
End Sub
 

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,345
Office Version
365, 2010
Platform
Windows, Mobile
Is filtering (either manually or with code) on doesn't equal zero an option?
 

JamesGilchrist

New Member
Joined
Apr 12, 2011
Messages
13
Unfortunately it is the result of a formula. What about that it must only apply for rows 10 to 50000?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,977
Office Version
2013
Platform
Windows
Maybe then...

Code:
Sub MM2()
Range("a10:a50000").AutoFilter Field:=1, Criteria1:="<>0"
End Sub
 

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,345
Office Version
365, 2010
Platform
Windows, Mobile
Hi Michael, sure not A9 rather than A10 for the header row ;)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,977
Office Version
2013
Platform
Windows
@MARK858
Good point, but I'm sure the OP can make that call....:LOL:
 

Forum statistics

Threads
1,078,546
Messages
5,341,093
Members
399,418
Latest member
joterde

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top