CODEWITHSUNDEEP
vbaexcel
R. de Rojas
R. de Rojas

Reputation: 107

Code for looping through a variable range fails when the range is one

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

Answers (1)

Pᴇʜ
Pᴇʜ

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

Related Questions