-2

I am using the following code to determine the next empty row on a worksheet:

emptyrow = WorksheetFunction.CountA(Range("B:B")) + 1

However, I have noticed that it only works when the page I want to use is activated. I have tried using with statements as so:

With MyWorksheet
emptyrow = WorksheetFunction.CountA(Range("B:B")) + 1
End With

But realized almost as soon as I had finished typing it that that isn't the correct usage of with. How can I perform worksheetfunctions without activating the worksheet? I am looping through many worksheets and don't want each to activate. Thanks!

GeorgeSBF
  • 33
  • 1
  • 7
  • 2
    You're missing the leading period which ties your `Range` to the worksheet object: `WorksheetFunction.CountA(.Range("B:B")) + 1` This (using CountA) is not reliable if you might have empty cells in your data though... – Tim Williams Aug 15 '17 at 20:15
  • `Set MyWorksheet = Worksheets("Name")` instead of `Set MyWorksheet = ActiveWorksheet` – Plagon Aug 15 '17 at 20:18
  • Also see [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Plagon Aug 15 '17 at 20:19
  • @TimWilliams Thank you! I should have thought of that. – GeorgeSBF Aug 15 '17 at 20:22
  • @UGP I already do Set MyWorksheet = Worksheets("Name") – GeorgeSBF Aug 15 '17 at 20:23

1 Answers1

1

As TimWilliams said in his comment, I just had to add a period before

range

to make it

.range

and it works.

GeorgeSBF
  • 33
  • 1
  • 7