Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Macro to hide rows based on cell value

This is a discussion on Macro to hide rows based on cell value within the Excel Questions forums, part of the Question Forums category; Like to create a macro (button) to hide / unhide rows based on a cell value Example A1=No then hide ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Detroit, MI
    Posts
    934

    Default Macro to hide rows based on cell value

    Like to create a macro (button) to hide / unhide rows based on a cell value
    Example
    A1=No then hide roa A
    C1=No then hide row c

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285

    Default Re: Macro to hide rows based on cell value

    How many rows are involved? Or do you want it to apply to the whole spreadsheet?

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Detroit, MI
    Posts
    934

    Default Re: Macro to hide rows based on cell value

    It would affect any row that has the value of "No" in a cell. It would then only show those rows where the reponse was "Yes"

  4. #4
    Board Regular LTunnicliffe's Avatar
    Join Date
    May 2003
    Location
    Round Rock, TX
    Posts
    767

    Default Re: Macro to hide rows based on cell value

    Try:

    Code:
    Sub HideNos()
      For i = 1 to Range("A65536").end(xlup).Row
        If UCase(Cells(1, i).value) = "NO" then Cells(1, i).entireRow.Hide
      Next i
    End Sub
    I wrote this then got confused because you say "A1=No then hide row A" but A is a column. Do you want to hide the columns? Or do you want to hide row 1, and if A3=No then hide row 3? My code goes through column A and hides all of the rows where the value of A equals to No.

    Hope this helps!
    Loren

    The numbers don't lie, but sometimes they don't tell the whole truth.

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Detroit, MI
    Posts
    934

    Default Re: Macro to hide rows based on cell value

    My mistake, if the cell value matches "no" hide that ROW, soory

  6. #6
    Board Regular LTunnicliffe's Avatar
    Join Date
    May 2003
    Location
    Round Rock, TX
    Posts
    767

    Default Re: Macro to hide rows based on cell value

    Then try mine and see what you think.
    Loren

    The numbers don't lie, but sometimes they don't tell the whole truth.

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Detroit, MI
    Posts
    934

    Default Re: Macro to hide rows based on cell value

    Changed the code to

    Sub HideNos()
    For i = 1 To Range("A500").End(xlUp).Row
    If UCase(Cells(i, 5).Value) = "NO" Then Cells(i, 1).EntireRow.Hide
    Next i
    End Sub

    But, it goes to debug and highlights the " Cells(i, 1).EntireRow.Hide " segment

  8. #8
    Board Regular LTunnicliffe's Avatar
    Join Date
    May 2003
    Location
    Round Rock, TX
    Posts
    767

    Default Re: Macro to hide rows based on cell value

    Change

    .Hide

    To

    .Hidden = True

    Sorry!
    Loren

    The numbers don't lie, but sometimes they don't tell the whole truth.

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Detroit, MI
    Posts
    934

    Default Re: Macro to hide rows based on cell value

    Just for your info, I found an alternate method that also works

    Sub HideRows()
    Dim cell As Range
    For Each cell In Range("e:e")
    If UCase(cell.Value) = "NO" Then
    cell.EntireRow.Hidden = True
    End If
    Next
    End Sub



    Thanks for the help, great forum

  10. #10
    Board Regular
    Join Date
    Feb 2008
    Posts
    51

    Default Re: Macro to hide rows based on cell value

    Five and a half years later, I dont know if the guys that started this thread are still around, but I have a supplimentary question.

    Is it possible to adapt the code below so that if it is run more than once it analyses all previously hidden rows and reinstates them if all rows do not contain "No"?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com