Reliable way to increase size of range

Hello everyone,

I am finding that I use a lot of .end(xlup / xldown) in my code nowadays to increase the size of a range when the exact length of a list is variable.

I tend to do this:

Please Login or Register  to view this content.

This method gives me the right range address in the immediate window every time (I type ?range("a1","c" & cells(200,1).end(xlup).row).address and it gives me the correct range of cells without fail). I can even reverse it and say:

Please Login or Register  to view this content.

this one is prone to the obvious downfalls, namely that if the range in A1 only contains one entry, it returns the entire colA as the address!

Here's the thing, when I run the sub from the button on my worksheet, both of these methods fail SOMETIMES (not always). I am asking if anyone knows both why, and whether there are any better ways to capture a list of variable size (from 1 item plus header, to 199 items plus header)?

I have done some testing and the above codes both fail seemingly randomly. I tested with lists = 1,2,25,100 items and there isn't a pattern i can find!


extra detail (just in case any of it is important):

for the sub, screenupdating = false. The sub populates the list, starting at row 3 with a header row at row 2. The sub then uses the MyRange variable to resize a range on a new workbook (workbook.add) and moves the values over (NewRange.value = Myrange.value).
The result I was getting which prompted me to start this thread was that I had two items in the list. Using the above "xlup" code example the result in the new book would be that the contents of row 1 (a title, not needed) and row 2 (the column headers) was all that was showing and the two list items were not present.

If there is any extra information i can provide, i will gladly do so

I notice you have no worksheet/workbook references in the posted code.

That means the code will refer to the active worksheet/workbook which might not be what you intend the code to refer to.

Thank you for the reply,

In fact my code looks like this:

Please Login or Register  to view this content.

Since I am soon going to be working with a new sheet that the sub has created, i thought it best to be explicit with the workbook and sheet that i was working with