| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to write a macro to hide an entire row
Hello guys,
I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro to hide an entire row
Hi,
Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value < "" _ And sht.Cells(x, 3).Value = 0 Then sht.Rows(x).Hidden = True End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jimmy" wrote: Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro to hide an entire row
Jimmy, heres another approach which could be used in any sheet to hide rows
with 0 or blank.. Sub HideRows() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row For lngRow = 1 To lngLastRow If WorksheetFunction.CountIf(Rows(lngRow), 0) + _ WorksheetFunction.CountBlank(Rows(lngRow)) = _ Columns.Count Then Rows(lngRow).Hidden = True Next End Sub -- Jacob (MVP - Excel) "Mike H" wrote: Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value < "" _ And sht.Cells(x, 3).Value = 0 Then sht.Rows(x).Hidden = True End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jimmy" wrote: Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro to hide an entire row
Mike, sorry my post ended up as a response to your one..which was unintended
"Mike H" wrote: Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value < "" _ And sht.Cells(x, 3).Value = 0 Then sht.Rows(x).Hidden = True End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jimmy" wrote: Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro to hide an entire row
Jacob,
No worries. I would include a test for a blank cell in your code, the OP seemed to be specific in wanting to hide rows with zero. Mike -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Jacob Skaria" wrote: Mike, sorry my post ended up as a response to your one..which was unintended "Mike H" wrote: Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value < "" _ And sht.Cells(x, 3).Value = 0 Then sht.Rows(x).Hidden = True End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jimmy" wrote: Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro to hide an entire row
OOPS,
Forgot, congratulations on being appointed MVP, well done and well deserved. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Jacob, No worries. I would include a test for a blank cell in your code, the OP seemed to be specific in wanting to hide rows with zero. Mike -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Jacob Skaria" wrote: Mike, sorry my post ended up as a response to your one..which was unintended "Mike H" wrote: Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value < "" _ And sht.Cells(x, 3).Value = 0 Then sht.Rows(x).Hidden = True End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jimmy" wrote: Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro to hide an entire row
Thanks. I understand the OP is only looking for zeroes.
"Mike H" wrote: Jacob, No worries. I would include a test for a blank cell in your code, the OP seemed to be specific in wanting to hide rows with zero. Mike -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Jacob Skaria" wrote: Mike, sorry my post ended up as a response to your one..which was unintended "Mike H" wrote: Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value < "" _ And sht.Cells(x, 3).Value = 0 Then sht.Rows(x).Hidden = True End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jimmy" wrote: Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro to hide an entire row
Thank you JAcob for your help.
Jimmy "Jacob Skaria" wrote: Jimmy, heres another approach which could be used in any sheet to hide rows with 0 or blank.. Sub HideRows() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row For lngRow = 1 To lngLastRow If WorksheetFunction.CountIf(Rows(lngRow), 0) + _ WorksheetFunction.CountBlank(Rows(lngRow)) = _ Columns.Count Then Rows(lngRow).Hidden = True Next End Sub -- Jacob (MVP - Excel) "Mike H" wrote: Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value < "" _ And sht.Cells(x, 3).Value = 0 Then sht.Rows(x).Hidden = True End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jimmy" wrote: Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to write a macro to hide an entire row
Thank you mike for your help.
Jimmy "Mike H" wrote: Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value < "" _ And sht.Cells(x, 3).Value = 0 Then sht.Rows(x).Hidden = True End If Next End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jimmy" wrote: Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How I write "if cell value =1, copy entire row & paste at dest. | Excel Programming | |||
| Hide Entire Row If Cell = 0 | Excel Programming | |||
| Hide an entire tab. | Excel Worksheet Functions | |||
| Hide an entire row - Not working | Excel Programming | |||
| is it possible to execute write to the fields in another .xsl form a macro in another .xsl? e.g. some way to load another .xsl into an .xsl macro and write to its data? | Excel Worksheet Functions | |||
Linear Mode
