0

I'm trying to store LastRow as an Integer but keep getting "Run-time error '9': Subscript out of range". The sheet I need it to work in is "Self-Service Report". The code I'm using is:

Dim LastRow As Integer

LastRow = ThisWorkbook.Sheets("Self-Service Report").Cells(Rows.Count, 1).End(xlUp).Row
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Conor
  • 3
  • 2

1 Answers1

2

  1. Excel has more rows than Integer can handle, therefore row counting variables need to be Long

  2. Rows.Count should specify to the worksheet

  3. Your worksheet name Self-Service Report seems to be wrong. Check for spelling and extra spaces.

  4. Worksheets is not equal to Sheets. Worksheets contain only worksheets but Sheets can also contain Charts for example.

Dim LastRow As Long

With ThisWorkbook.Worksheets("Self-Service Report")
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Hmmm... I put that line of code in, checked the spelling/spaces, but I'm still getting the error – Conor May 21 '19 at 14:37
  • @Conor you get the out of range in the `With …` line? If yes then the worksheet name does *definitly* not exist in `ThisWorkbook` (which is the workbook the code is running in). – Pᴇʜ May 21 '19 at 14:39
  • Ah, I found the issue. I was doing this in my Personal.XLSB Is there a way to make the code work with it in the personal file, rather than the actual workbook? – Conor May 21 '19 at 14:43
  • If you have the code in `Personal.xlsb` and you want to run it in the active workbook use `ActiveWorkbook` (the one which has focus) instead of `ThisWorkbook` (the one the code is in). – Pᴇʜ May 21 '19 at 14:45
  • It works now. Thank you! – Conor May 21 '19 at 14:46