Hiding Rows Based on Data or Blanks

[WireGuy8]

I have a cell that has text data that filters the range I need. It works great when there is data in there. When that cell is blank, is does not filter out the range that has data.

sheet10.Activate
Range("D3").AutoFilter Field:=4, Criteria1:= _
"=*" & Range("Q1").Value & "*", Operator:=xlAnd

How can i get it to filter based on the data in the cell, but hide rows in the range when the cell is blank. The cell Q1 is the cell with the data that needs to be filtered and if that cell is blank, hide rows with data.

 

[Fluff]

Do you mean you want to hide all rows if Q1 is blank?

 

[WireGuy8]

Yes, hide all rows 3-502

 

[Fluff]

Ok, how about

   With Range("Q1")
      If .Value = "" Then
         Range("D3").AutoFilter 4, "", xlAnd, "<>"
      Else
         Range("D3").AutoFilter 4, "*" & .Value & "*"
      End If
   End With

 

[WireGuy8]

Your post would work if there was not a if isblank statement. To get around that I might change the Q1 to B5 on sheet7 and still do the filtering on sheet 10 from my original post.

 

[Fluff]

What do you mean by

if there was not a if isblank statement

 

[WireGuy8]

I just ran the code you had posted, it is not hiding the rows if it is blank

 

[Fluff]

Is there a formula in those cells?
If so what is it?

 

[WireGuy8]

Here is the formula in Q1 on sheet 10.

=IF(ISBLANK('Shield Wire'!B5),"",'Shield Wire'!B5)

I can also just use B5 from sheet 7 as the Reference data and then filter on sheet 10 as shown in my first post. I am just having problem getting this to work.

 

[Fluff]

Thanks for that, however it should not make any difference. The data should still be hidden.
Make sure that B5 is actually empty (no stray spaces etc), also do you have a formula in col D?