[RESOLVED] Total records returned by advancedfilter

[scoobster]

Hi Forum

I need to check if any rows were returned by an advancedfilter. If any are returned - doesn't matter how many - then I will do some processing otherwise I just exit.

The problem is that although I see records returned under the header row, all I get is a count of 1 in row.count. I presume this is related to contiguous ranges within the returned data.

All I need is a way to workout if at least one row was returned by the filter.

Code is below;

With worksheets("sheet2").Range("Products[#All]")
       .advancedfilter Action:=xlFilterInPlace, CriteriaRange:=worksheets("sheet4").Range("C1:C" & PCFilterLastRow), Unique:=False
            
' returned count will always be at least 1 as this is the header so if any data returned then count will be >1
       If worksheets("sheet2").Range("Products").Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
             Set rng = worksheets("sheet2").Range("Products[PC]").SpecialCells(xlCellTypeVisible)
              .... do some processing

       End if

end with

I have searched and found a few posts on this and this is what drove me to use the code shown about but it's not working exactly as I need it to. Any ideas?

Thanks

[vbfbryce]

Sub findlast()
    Dim myRow As Long
    myRow = Range("a65536").End(xlUp).Row   'sub in whatever column you want to check
    If myRow > 1 Then    'assumes headers in row 1
        MsgBox "At least one record returned"
    End If
End Sub

[scoobster]

Thanks for replying to my post.

I tried and the row is always set as the one below the end of range so the result is always going to be greater than 1.

Just curious as to how I implement it using the range 'Product' rather than hardcoding 'A65536' as a cell?

I read a quite a few example where people had the '.specialcells(xlcelltypevisible).rows.count' as the way to count after filtering but I couldn't get it to work. What would you usually use when counting after a filter?

[scoobster]

Hi

Is it better to use 'resume next' rather than trying to find out the number of records returned? Ideally I would like to have a way to find the number of records in the filtered range as I can then displayed that and also trap for error when 0 records are returned.

Thanks.

[vbfbryce]

I don't understand why the code I posted below doesn't work for your purposes.

Take a look at the attached book. I have a simple filter set up (not an advanced filter via code), but I think the behavior should be the same.

I have a filter currently set so that it returns no records (a particular value both equals 1 and does not equal 1). Run the macro that way. Then change the filter to something else that does return records, and run it again.

It's been a while, so I probably need to catch back up a bit on what you were trying to do, but take a look and let me know.

[scoobster]

I think it is my error that causes this not to work. I have adapted your code to the following so as to incorporate my RANGE rather than relying on the
A65536 cell.

My code:

myRow = worksheets("sheet2").Range("Products[BA]").End(xlUp).Row

So i'm not actually going to the end of the range, am I? So the END+UP is not going to work. Is there a way I can incorporate the END+UP and still use the RANGE? I guess I'm saying is there another way to work rather than use the A65535 & END+UP method.

Thanks as always.

[vbfbryce]

Is your range (Products) one column, or more than one?

If just one, is it always the same? If more than one, is the left most column in the range always the same?

[scoobster]

The range PRODUCT is multiple columns.

The left most column is always the same column heading (BA) but the length (i.e. number of rows) can vary as more rows are added.

[vbfbryce]

Gotcha.

Use my code from above and change this:

myRow = Range("a65536").End(xlUp).Row   'sub in whatever column you want to check

to this:

myRow = Range("BA65536").End(xlUp).Row   'sub in whatever column you want to check

Would that eliminate the need to use the range named Products?

[scoobster]

Thanks

Sure, I can use that and drop the reference to the RANGE for this particular check. I was just looking to be consistent as I have used ranges everywhere else in the code. Just want to be a tidy coder and use modern techniques

Many thanks again for all your replies and support.