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

Question

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))

This answer gives a nice explanation of `xlDown`: https://stackoverflow.com/a/9405164/9259306 — Mistella, Aug 01 '18 at 12:53

score 1 · Accepted Answer · answered Aug 01 '18 at 12:51

1

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))

answered Aug 01 '18 at 12:51

Pᴇʜ

56,719
10
49
73

Perfect! Works! Thank you – R. de Rojas Aug 01 '18 at 13:08

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

1 Answers1