Macro to hide rows based on cell value

[UHsoccer]

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

 

[PCRIDE]

probaly just run another check and use the .unhide command.

 

[enyche]

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

This works for me but...it take a long time to run down the colum. Have around 2000+ lines. Is there something that will be faster? Thanks.

 

[Recover]

Hello,

I would like to piggy-back on this thread but it accomplishes a similar task....

How would I write the following code to prompt the user for a column? Or better yet prompt for a range within a column (ex. B7:B55). Since the column may not always be B:B.

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


Thanks for your assistance!

 

[tartangeek]

Hi

I would like to use this code with a spreadsheet I have where if after a certain date...say 1/1/11 the rows automatically hide themselves or even better archive themselves to another worksheet! Have tried adapting the code in the posts above to no avail!

Thanks

 

[DWall]

Hello,

I would like to piggy-back on this thread but it accomplishes a similar task....

How would I write the following code to prompt the user for a column? Or better yet prompt for a range within a column (ex. B7:B55). Since the column may not always be B:B.

Simply use an InputBox:

Sub HideRows()
Dim cell As Range
Dim PromptedValue As String
PromptedValue = InputBox("Please enter the column you wish to use (eg. A:A, A1:A100 etc.", "Input Column", "A:A")
If PromptedValue = "" Then
    Exit Sub
Else
    For Each cell In Range(PromptedValue)
    If UCase(cell.Value) = "" Then
    cell.EntireRow.Hidden = True
    End If
    Next
End If
End Sub

P.S. using whole columns (ie A:A) isnt very useful as it will hide everything right down to A625570... or what ever the bottom most cell is. if you want to just do all the data in the sheet you would have to put a little more code (which i can't remember off the top of my head).

 

[DWall]

Hi

I would like to use this code with a spreadsheet I have where if after a certain date...say 1/1/11 the rows automatically hide themselves or even better archive themselves to another worksheet! Have tried adapting the code in the posts above to no avail!

Thanks

It worked when i tried, remember to put "01/01/2011", dont shorten it

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

Again ill say, try not to use whole columns ("B:B") as it will work down all the rows right to the bottom of the sheet, this takes quite a while, use a limited range ("B1:B2000").

I dont know what you want to do when 'archiving', do you want them taking out of the original sheet alltogether or just hiding in first sheet then copying into second?

If so just 'cut and paste' or copy the cells before hiding them respectively.

There are many ways to do it :P

 

[UKNN]

Thanks to all, another great solution that's helped me.

Ian.

 

[Londinium]

Hi,

How would you achieve the reverse - e.g. code so that the four rows following the row in which the cell is with a value lies, are hidden unless a value of "Yes" appears in that cell?

Essentially looking at creating a template which has additional rows which become visible if the user answers with a "yes" in a particular cell. Obviously a "No" in the cell would keep the following four rows hidden.

Thanks for any answers.

 

[hhebe]

Hi,

How would you achieve the reverse - e.g. code so that the four rows following the row in which the cell is with a value lies, are hidden unless a value of "Yes" appears in that cell?

Essentially looking at creating a template which has additional rows which become visible if the user answers with a "yes" in a particular cell. Obviously a "No" in the cell would keep the following four rows hidden.

Thanks for any answers.

I am running into this scenario too - any takers?