macro that hides rows - excel

macro that hides rows - excel

• Brendan Egan

  hi

  Hiding rows is quite simple, the below hides row 1 is A1 is 0, you can also choose to hide the Rows/Columns in a range in a similar way

  With ActiveSheet
  If .Cells(1, 1) = 0 Then .Rows(1).Hidden = True
  End With

  
  

• develronioder

  Yeah this works pretty good! Problem is, i've got 1500 cells to write this macro for individually...you mentioned a way to chose them in a range of cells, how can I modify this to make it work I tried a couple of ways of changing it..thinking it would select a range similar to Excel selects a range, but with no luck. Thanks.
  
  

• mizuno

  Hi again

  Try

  
  Sub Update()

  Dim lngLastRow, lngRow As Long
  With ActiveSheet

  lngLastRow = .Range("I1").End(-4121).Row
  For lngRow = 1 To lngLastRow
  If .Cells(lngRow, 9) = "a" Then .Rows(lngRow).Hidden = True
  Next
  End With

  End Sub

  
  

• NickUk

  Hmm..what this did was make the macro have to be run seperately for each row. I had to keep clicking the button I set up to continue on down through the column. Also, when I got to a "b" value, it still wouldn't loop past it. Thanks.
  
  

• Ninja

  If you need to look at each cell individually you will need to loop through them. If they are in one column and consecutive then this is not too difficult. The below loops through from A1 to the last row used in the sheet.

  Public Sub MySub()

  Dim lngLastRow, lngRow As Long

  With ActiveSheet

  lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

  For lngRow = 1 To lngLastRow

  If .Cells(lngLastRow, 1) = 0 Then .Rows(lngLastRow).Hidden = True

  Next

  End With

  End Sub

  If you want to find the last non blank entry in a column use the below ( this is like pressing end down in cell A2)

  lngLastRow = .Range("A1").End(-4121).Row

  Change A1 to the relevant column / start row

  
  

• YU_MSVB

  I got it! Here's the code that makes it all work:
  
  Sub Update()
  
  Dim lngLastRow, lngRow As Long
  With ActiveSheet
  
  lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).row
  For lngRow = 1 To lngLastRow
  If .Cells(lngRow, 9) = "a" Then .Rows(lngRow).Hidden = True
  
  Next
  End With
  
  End Sub
  
  
  Thanks for all your help.
  
  

• @nt

  I seem to be having a problem. Every time it comes to a value that isn't "a", the loop ends. Basically that other value is a "b". I need this to evaluate the "a" and "b" terms and decide if it should hide or not hide the row and continue through to the last cell or a defined cell in the column. I tried putting in this:
  
  "If .Cells(lngLastRow, 9) = "b" Then .Rows(lngLastRow).Hidden = False"
  
  thinking maybe that if it had a false statement it would continue on through but that didn't work. What am I doing wrong
  
  
  
  Sub Update()
  
  Dim lngLastRow, lngRow As Long
  With ActiveSheet
  
  lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).row
  For lngRow = 1 To lngLastRow
  If .Cells(lngLastRow, 9) = "a" Then .Rows(lngLastRow).Hidden = True
  
  lngLastRow = .Range("I1").End(-4121).row
  
  Next
  End With
  
  End Sub
  
• macro that hides rows - excel