Reputation: 107
I have a problem with some of my code. I basically wrote a line that would allow me to define a range which is variable in size. This is so that my model creates sheets for each element in the range. This code works prefectly when the range defined is bigger than one, however when im supposed to define a range of one or zero my code tells me there is a large number of elements (like to 100,000) in this range. Anyone know why? This is the code:
Dim deposits As Range
Set deposits = Worksheets("DATA ENTRY").Range("C3", Worksheets("DATA ENTRY").Range("C3").End(xlDown))
Upvotes: 2
Views: 40
Reputation: 57753
The issue is that xlDown
will move down to the very last row of Excel if there is no data in C4 or below.
Therefore use xlUp
starting from the very last row Rows.Count
to determine the last used cell. This is more reliable than the xlDown
method.
Dim deposits As Range
Set deposits = Worksheets("DATA ENTRY").Range("C3", Worksheets("DATA ENTRY").Cells(Rows.Count, "C").End(xlUp))
Upvotes: 1