PROBLEM #1: UsedRange.Rows.Count
I know a lot of people do it, but you should never use the
UsedRange method to determine last row of data. It's not doing what you think it's doing, even though it works 95% of the time. And when it fails you won't know why, or worse, you won't know it's happened and rows of data are disappearing off your database. Oops!
Try this. Open a blank workbook. Go to cell A3 and enter anything.
Now open the VBEditor and CTRL-G to open the immediate window. Now enter this line of code from your macro:
Code:
? ActiveSheet.UsedRange.Rows.Count
The Immediate Window will respond with a 1 instead of 3. That's because it's counting the number of rows in the USEDRANGE, it's not telling you the last row with data. It's not the same thing
at all.
OPTIONAL PROBLEM #2: LastCell
If you Google for other options, you might stumble on to the
xlLastCell method. But this is also a problem because Excel is awful at remembering what the ACTUAL last used cell is on a worksheet, something it
should be good at.
Well, it's not. Don't fall for that method either.
==========
So, lesson #1, here are my two favorite code snippets for determining the last row of data...or in your case the next empty row right below that.
1) Look UP a specific column, typically column A
Code:
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
2) Quickly check all cells on the activesheet and find the last row with anything anywhere actually in a cell.
Code:
NR = Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
There are good reasons to use each of the two methods above, #1 is my most frequent because I know which columns are critical and always have data in them, so I know which column to base the search on.
=========
Now, as to your macro, can you explain in a non-code-way what you're actually trying to do. I can see several issues with the code and fixing it properly (and efficiently) requires I better understand in words the full process you're going through.
A sample workbook with your desired BEFORE/AFTER examples would be the most clarifying.