0

I have a spreadsheet with column "A" populated like so:

A
1 Some Header
2 name1
3 name2
4
5 name3
6 name4
7
8 name5

In a subroutine in VBA I want to store the range "A2:A8" in a variable, but I'm looping through multiple sheets and I don't know the last row that contains data a priori. So it could be A8 or A56 for example. Here's what I have so far:

Sub someSub()
Dim wb As Workbook
Dim i As Integer
Dim srcRange As Range

shCount = wb.Worksheets.Count
For i = 1 To shCount
    Set srcRange = wb.Worksheets(i).Range("A2", wb.Worksheets(i).Range("A3").End(xlDown))
    'Do some amazing stuff~
Next

End Sub

However, this is throwing a "1024" error (I think, I didn't write the number down). Even if it worked, I believe "xlDown" will only go to A3 since A4 is an empty cell. Hard-coding the range in like this works: Set srcRange = wb.Worksheets(i).Range("A3:A8"), but again, I don't know the max row number beforehand.

Additionally, and unrelated, but I want to lock a column so nobody can edit it, unless a condition is met (that condition being that the value for some cell is 1).

Thank you!

BrandonC
  • 1
  • 1
  • `With wb.Worksheets(i)`, `Dim lastRow As Long`, `lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row`, `Set srcRange = .Range("A2:A" & lastRow)`, `End With`. – BigBen Jun 17 '21 at 01:17
  • Set srcRange = wb.worksheets(i).Range(Cells(3,1),Cells(wb.worksheets(i).UsedRange.Rows.Count,1)). Worksheet.UsedRange is very useful for finding the Excel rectangle containing data. – Chris Maurer Jun 17 '21 at 02:06
  • With the caveats around `UsedRange` in the linked thread. It's not reliable. – BigBen Jun 17 '21 at 02:43

0 Answers0