Interesting problem: Auto filter based on other column

[mathsbeauty]

Hello! I am new here. I am learning macros in excel. I want to develop macro which will auto filter column B based on the entries of column A. This is interesting! I want to hide those cells in B which contains "WW" or "TT" in the corresponding entries of column A. e.g. if cell A2 contains "WW" then row 2 must be hidden.

 

[syntaxed]

Does this achieve the desired result?

Sub HideRow()
For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If Cells(i, 1) = "WW" Or Cells(i, 1) = "TT" Then Rows(i).Hidden = True
Next
End Sub

 

[mathsbeauty]

THANKS syntaxed! It works. Can you please explain how "1 Step -1" works?

 

[mathsbeauty]

One more thing. This macro hides correctly. But I also want to hide blank cells.

 

[syntaxed]

You could try:

Sub HideRows()
For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    Select Case Cells(i, 1).Value
        Case "WW", "TT", VBA.vbNullString
            Rows(i).Hidden = True
        Case Else
            'leave row as it is
    End Select
Next
End Sub

The above code will hide rows where the value in column A is blank or "WW" or "TT".

Range("A" & Rows.Count).End(xlUp).Row

This identifies the last row in column A, then uses the equivalent of pressing "End + up-arrow" to identify the row of the last used cell in column A.

For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1

This then loops backward one at a time (i.e. Step -1 means backward one at a time) from the row identified all the way to row 1.

 

[mathsbeauty]

THANKS Syntaxed! I understood the code. But surprisingly it is not hiding the blank rows column A.

 

[syntaxed]

Then it appears the cells are not truly blank. They may look blank but actually contain a space.

If that is the case, use this instead:

Select Case Trim(Cells(i, 1).Value)

 

[mathsbeauty]

Hello! I got the problem. "End(xlUp).Row" gives the last used row. It does not give really the last row of worksheet. My problem is to hide all unused rows. Getting this, I mean if I have used only first 100 rows then "End(xlUp).Row" will give 100th row. Your macro will hide all rows which contains blank cells in column A. Then it will display 101th ,102th,.... rows. I want to hide these unused rows. Can this be possible? Thanks for all your efforts.

 

[syntaxed]

Sure, just add this before End Sub:

Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1 & ":A" & Rows.Count).EntireRow.Hidden = True

This'll hide all the rows at the bottom in which column A was unused.

 

[Peter_SSs]

I'm assuming ..

a) Headings in row 1

b) No blanks within the data in column A.

If those are correct, try this method that doesn't require looping through the used rows in column A.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Hide_Rows()<br>    <SPAN style="color:#00007F">With</SPAN> Range("A1", Range("A" & Rows.Count).End(xlUp))<br>        .AutoFilter Field:=1, Criteria1:="<>TT", Operator:=xlAnd, Criteria2:="<>WW"<br>        Rows(.Offset(.Rows.Count).Row & ":" & Rows.Count).Hidden = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Just an observation on the previous codes suggested. If deleting rows by looping through them you need to work from the bottom up (step -1). However, for hiding rows that requirement doesn't exist. Nothing wrong with looping backwards, it's just that it isn't necessary.