1

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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Joel Bastien
  • 121
  • 2
  • 11

1 Answers1

2

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"))
Tate Garringer
  • 1,509
  • 1
  • 6
  • 9
  • 1
    This is the answer. Note that Excel automatically flips `A2:A1` to `A1:A2` (try it in a cell!). Thus, OP needs to explicitly make `count` be `0` when `lastRow` is `1` - otherwise they're counting their header row. Alternatively, subtotal `"A1:A" & lastRow`, and systematically subtract 1 from that. – Mathieu Guindon Jan 17 '19 at 21:39