Dynamic Range Trouble...rows arent updating??

Sorry Mojo, two things...

1) Never use .UsedRange to determine rows, it "seems" to work most of the time, but it's not working for the reasons you think, so when it fails you won't know it has, and you're data is now corrupted. Best to avoid using .UsedRange method for determining last row of data.

2) We need to show OPs how to remove the "selecting" and "activating" from their recorded macros 99% of the time.


===============

My solution would use a variable to spot and store the row number of the last row with data looking UP column A:

Please Login or Register  to view this content.

If you want to see the .UsedRange.Rows.Count method fail, try this:

1) Open a new empty sheet
2) Place a value in C5 only
3) Open the VBEditor
4) Open the Immediate window (Ctrl-G) and type in this line of code to get the window to show you the answer to your line of code:

Please Login or Register  to view this content.

Notice it gives back the answer 1 instead of 5? UsedRange.Rows.Count tells you how many rows are used, NOT what the last row used is. Any blank rows at the top of a sheet will completely destroys this method.

You could get all fancy and spot the first row used and do some math to make it work, but it's confusing to look at and more calcs than needed:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by mojo249

I normally would use .End to find the last row in my own projects. But in answering this question I used .UsedRange because I couldn't guarantee that the last row would always be in column A.

True, but from a teaching standpoint it's much simpler to novice vba writers to grasp editing the correct column to "look up". It's an easy lesson.

Am I also right in thinking that .UsedRange will count formatted cells (even if the cell is empty)?

You are correct.



SNB shows another good method, but be wary the .CurrentRegion method, too. That will fail if your data has any fully blank rows in the data. If not, and all rows have at least one cell with data, then that would be a great option.

@JB

100% agreed.

Just as a question could we state that we can use usedrange 'safely' if

Please Login or Register  to view this content.

we can use currentregion 'safely' if

Please Login or Register  to view this content.

SNB, absolutely.

I'm actually a fan of the one-liner solutions you usually strive for, but in truth, I find them impossible to trust since I've taken to "defensive coding" in a lot my macros, the macros solve a standard problem of some sort, but usually require a few extra lines of code to resolve common problems (or test for them) along the way. You know how it goes...