0

I'm getting

"1004 application-defined or object-defined error"

I need to count rows containing data beginning at Cell("A2") of multiple worksheets.
I tried several combinations.

How do I put this worksheet("Sheet1") reference into the instruction?

Dim oRows As Long
Dim oRows2 As Long
'...

oRows = WorksheetFunction.CountA(Worksheets("Sheet1").Range("A2", Range("A2").End(xlDown)))
oRows2 = WorksheetFunction.CountA(Worksheets("Sheet2").Range("A2", Range("A2").End(xlDown)))

If there's just data in "A2" the following code returns "1048575" instead of "1".

oRows = ActiveWorkbook.Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A2").End(xlDown)).Rows.Count

So, I need to use CountA function.

Community
  • 1
  • 1
Xander
  • 15
  • 6
  • How about .... Range("A2", Range("A1").End(xlDown))? – DS_London Oct 16 '20 at 16:24
  • `WorksheetFunction.CountA(Worksheets("Sheet1").Range("A:A"))-1`? – BigBen Oct 16 '20 at 16:25
  • 2
    Note that [this is the correct approach to find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Oct 16 '20 at 16:26
  • The only problem here is that I need to put the " **Worksheets("Sheet1")** " reference into the instruction but I don't know how... – Xander Oct 19 '20 at 16:00

1 Answers1

0

The following will count all cells in column A that are not empty, then subtract 1 for cell A1 if it is not empty:

oRows = WorksheetFunction.CountA(Range(“A:A”)) - WorksheetFunction.CountA(Range(“A1”))

Dustin
  • 25
  • 7