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.

 

[WireGuy8]

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?

Column D does not hat anything. It gets populated through use of macros and a button.

Please see the code I have to filter copy and paste.
sheet10.Activate
With Range("Q1")
If .Value = "" Then
Range("D3").AutoFilter 4, "", xlAnd, "<>"
Else
Range("D3").AutoFilter 4, "*" & .Value & "*"
End If

If .Columns(4).SpecialCells(xlVisible).Rows.Count = 1 Then Exit Sub
.AutoFilter.Range.Offset(1).Columns("D").Copy
sheet3.Range("K44").PasteSpecial xlPasteValues
.AutoFilter.Range.Offset(1).Columns("L").Copy
sheet3.Range("L44").PasteSpecial xlPasteValues
End If
End With

 

[Fluff]

With the code I supplied do all rows get hidden if Q1 is ""

 

[WireGuy8]

With the code I supplied do all rows get hidden if Q1 is ""

Yes, just trying to get the stuff to copy and paste

 

[WireGuy8]

Yes, just trying to get the stuff to copy and paste

Copy and paste only the visible rows

 

[Fluff]

If you want it to hide all the rows, then there is nothing to copy/paste.

 

[WireGuy8]

If you want it to hide all the rows, then there is nothing to copy/paste.

Correct. If the data in the table that matches Q1 will be filtered. That will be moved to another sheet.

If Q1 is blank and there is code to copy paste, but there is nothing to copy paste, how to move on to the next set up code.

Basically need it to filter... if the filter rows that match Q1 then the data will be copy and pasted to another sheet. If Q1 is blank no need to copy and paste exit this if/with code

 

[Fluff]

Ok, as this is now a completely different question to your op, it needs a new thread. Thanks