| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide Zero Values
Hello, I need to run a macro to hide any rows that contain a zero value in a certain column number. e.g hide all rows that contain a zero value in column B? Is this possible do you think? Thanks in advance Paul -- PaulOakley ------------------------------------------------------------------------ PaulOakley's Profile: http://www.excelforum.com/member.php...o&userid=25103 View this thread: http://www.excelforum.com/showthread...hreadid=529130 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide Zero Values
Sub Test()
Dim iLastRow As Long Dim i As Long Dim rng As rng iLastRow = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To iLastRow If Cells(i, "B").Value = 0 Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If End If Next i If Not rng Is Nothing Then rng.Hidden = True End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "PaulOakley" wrote in message ... Hello, I need to run a macro to hide any rows that contain a zero value in a certain column number. e.g hide all rows that contain a zero value in column B? Is this possible do you think? Thanks in advance Paul -- PaulOakley ------------------------------------------------------------------------ PaulOakley's Profile: http://www.excelforum.com/member.php...o&userid=25103 View this thread: http://www.excelforum.com/showthread...hreadid=529130 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide Zero Values
Typo:
Dim rng as Range HTH -- AP "Bob Phillips" a écrit dans le message de ... Sub Test() Dim iLastRow As Long Dim i As Long Dim rng As rng iLastRow = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To iLastRow If Cells(i, "B").Value = 0 Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If End If Next i If Not rng Is Nothing Then rng.Hidden = True End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "PaulOakley" wrote in message ... Hello, I need to run a macro to hide any rows that contain a zero value in a certain column number. e.g hide all rows that contain a zero value in column B? Is this possible do you think? Thanks in advance Paul -- PaulOakley ------------------------------------------------------------------------ PaulOakley's Profile: http://www.excelforum.com/member.php...o&userid=25103 View this thread: http://www.excelforum.com/showthread...hreadid=529130 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide Zero Values
Seems to be coming back with error msg of "user defined ype not defined" Dim rng As rng- this part is being highlighted, is this right, or should i be entering data here -- PaulOakley ------------------------------------------------------------------------ PaulOakley's Profile: http://www.excelforum.com/member.php...o&userid=25103 View this thread: http://www.excelforum.com/showthread...hreadid=529130 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide Zero Values
If Not rng Is Nothing Then rng.Hidden = True This is now showing as a debug error.. I know very little VBA, so dont seem to be able to change the error.. Thanks in advance -- PaulOakley ------------------------------------------------------------------------ PaulOakley's Profile: http://www.excelforum.com/member.php...o&userid=25103 View this thread: http://www.excelforum.com/showthread...hreadid=529130 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide Zero Values
Thanks mate.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... Typo: Dim rng as Range HTH -- AP "Bob Phillips" a écrit dans le message de ... Sub Test() Dim iLastRow As Long Dim i As Long Dim rng As rng iLastRow = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To iLastRow If Cells(i, "B").Value = 0 Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If End If Next i If Not rng Is Nothing Then rng.Hidden = True End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "PaulOakley" wrote in message ... Hello, I need to run a macro to hide any rows that contain a zero value in a certain column number. e.g hide all rows that contain a zero value in column B? Is this possible do you think? Thanks in advance Paul -- PaulOakley ------------------------------------------------------------------------ PaulOakley's Profile: http://www.excelforum.com/member.php...o&userid=25103 View this thread: http://www.excelforum.com/showthread...hreadid=529130 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide Zero Values
Paul,
Ardus pointed out a typo, and I need a bit more property Sub Test() Dim iLastRow As Long Dim i As Long Dim rng As Range iLastRow = Cells(Rows.Count, "B").End(xlUp).Row For i = 1 To iLastRow If Cells(i, "B").Value = 0 Then If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i)) End If End If Next i If Not rng Is Nothing Then rng.EntireRow.Hidden = True End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "PaulOakley" wrote in message ... If Not rng Is Nothing Then rng.Hidden = True This is now showing as a debug error.. I know very little VBA, so dont seem to be able to change the error.. Thanks in advance -- PaulOakley ------------------------------------------------------------------------ PaulOakley's Profile: http://www.excelforum.com/member.php...o&userid=25103 View this thread: http://www.excelforum.com/showthread...hreadid=529130 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to hide 0 values in pivot table | Excel Discussion (Misc queries) | |||
| hide 0 values in ptable | Excel Discussion (Misc queries) | |||
| Hide all values in a row if value is 0 | Excel Worksheet Functions | |||
| How do I hide 0 values in line charts | Charts and Charting in Excel | |||
| Excel - hide erroer values | Excel Discussion (Misc queries) | |||
Linear Mode
