copy autofilter values if not empty

[confused in Frankfurt]

Hi

i need help with a small problem.

have a thread which filters a dataset to show a criteria

wanted to use
ActiveCell.Offset(1, 0).Activate
ActiveCell.Select

If Not IsEmpty(Range(Selection).Value) Then
.....
end if

to determine whether there are any values returned using the autofilter before copying and pasting to skip this procedure to move on to the next if values not available.

The offset always moves from the titles row to the next row (not shown in the autofilter selection) ie header in row 3 then always shows row 4 even if the autofilter next row is 50.

How do I check if there are any values being shown in the autofilter with the desired criteria and skip if no values??

regards
Sarah

 

[JoeMo]

Try something like this:

Dim x As Long
x = WorksheetFunction.Subtotal(3, Range("your range here"))
If x > 0 Then
'rest of your code here

where your range is the unfiltered range.

 

[confused in Frankfurt]

Thanks Joe this works a treat