Search code examples
excelvbacountsubtotal

Why does my subtotal code counts 1 when there's no values?

so I'm running a code that filters my data and then takes the count of the data and inserts it into an excel file. I'm using this code to count the data: 

lastrow = Cells(Rows.count, "A").End(xlUp).Row
count = Application.WorksheetFunction.Subtotal(3, Range("A2:A" & lastrow))

For some reason, when I've filtered my data so that no value is visible aside from my top row, which is located in row 1, It gives me a value of 1 rather than 0. Any ideas why it's doing so?

Thanks

Solution

  • This is actually a funny thing I, too, discovered a while ago. What's happening is that, with a blank (or just filtered to appear blank) column A, lastrow = 1, which means the range you're Subtotaling is Range("A2:A1").

    For example, test:

    Debug.Print Application.WorksheetFunction.Subtotal(3, Range("A2:A1"))