How to write a macro to hide an entire row
in [Programming]
|
From: Mike H on 14 Apr 2010 05:50 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é
From: Jacob Skaria on 14 Apr 2010 05:51 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é
From: jimmy on 14 Apr 2010 08:03 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é
From: jimmy on 14 Apr 2010 08:03 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é
First
|
Prev
|
Pages: 1 2 Prev: Countif number between x and y Next: Working with a worksheet that is not the active worksheet |