Macro to Hide rows based on value of column F

[Guest]

I looked through all the similar questions but couldn't find the answer I'm
looking for. I would like to have two buttons. One to "Show Active Files".
This would hide all rows in which column F has either "Withdrawn" or
"Declined". And the other button would be "Show All Files". This would
unhide anything that is hidden.

If anyone has any suggestions it would be greatly appreciated.

 

[Don Guillett]

use data>filter>autofilter. Record a macro if desired. use autofilter again
or showall to remove the filter.

 

[Guest]

I actually already use autofiler for other purposes. I'm not sure how it
would work with the filter, unfilter, filter again. Plus, right now the
worksheet is blank. I suppose in order to record a macro where I rearrange
all the data and the select the ones that I want to hide, I would need to
make up at least some data to put in just for that purpose. I was hoping
there was a macro that I could assign to the two buttons that would just hide
the rows where "Withdrawn" or "Declined" is in column F or unhide them.

Thanks,
Scott

 

[Don Guillett]

An autofilter macro would be best but you could always use a loop

Option Compare Text 'put this line at the top of the regular module

Sub hiderowsif()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = lr To 2 Step -1
If Cells(i, "a") = "Withdrawn" Or Cells(i, "a") = "Declined" Then
Rows(i).Hidden = True
Next i
End Sub

 

[Guest]

I didn't realize that the autofilter would be part of the macro. How would I
do that without manually recording one? Also, if I go with the one you sent
me, what would be the macro for the "unhide" button?

Thanks again,
Scott

 

[Gord Dibben]

Scott

Assign each of these to your buttons.

Sub HideRows_2Params()
'Bob Phillips Aug. 26, 2006
'slight modifications
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "F").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "F").Value <> "" Or _
(Len(Cells(i, "F").Value) = "WithDrawn" Or _
Cells(i, "F").Value = "Declined") Then
Rows(i).EntireRow.Hidden = True
End If
Next i

End Sub

Sub UnHideRows()
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

You must be close, but this is what happens: it hides every row which has
anything in column F, not just the ones with "withdrawn" or "declined". The
unhide works fine.

Thanks

 

[Don Guillett]

Sub unhiderows()
Cells.Rows.Hidden = False
End Sub

 

[Gord Dibben]

Sorry Scott(and Bob)

Try this.

Option Compare Text
Sub HideRows_2Params()
'Bob Phillips Aug. 26, 2006
'modified by Gord Dibben
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "F").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "F").Value <> "" And _
Cells(i, "F").Value = "withdrawn" Or _
Cells(i, "F").Value = "declined" Then
Rows(i).EntireRow.Hidden = True
End If
Next i

End Sub


Gord

You must be close, but this is what happens: it hides every row which has
anything in column F, not just the ones with "withdrawn" or "declined". The
unhide works fine.

Thanks

Gord Dibben MS Excel MVP

 

[Guest]

This gives an error message saying "Compile error: next without for"

 

[Don Guillett]

change to ONE line or use end if as the line after