Gosh.
Two points to make, one about
Activecell, the other about
.UsedRange.
You can step through code using the
F8 key while observing the sheet it's acting on. You click somewhere in the code and you press
F8 on the keyboard.
Arrange the windows on your screen so that one half shows the sheet, and the other half the code (the VBE window).
Open the attached with sheet
UsedRange1 showing in one half and the macro
sequenceOptimizer1 in the other.
Don't make
any changes to the sheet, but select cell E1
Click in the macro code somewhere and press
F8 on the keyboard until the line:
LastRow = .UsedRange.Rows.Count
is highlighted in yellow. This is the line that is next going to be executed.
Before you press
F8 again, use your mouse to hover over the text
LastRow in the code. You should see a small pop-up with the text:
LastRow = Empty
This shows you the value in
LastRow.
Now press
F8 again, and once again hover over
LastRow, you should see:
LastRow = 14
When we glance at the sheet we indeed note that the last row is row 14. This is what we expect.
Now press
F8 again until the start of the first
If statement is highlighted.
(You should still only have cell E1 selected at the moment.)
While we're here we'll also hover over
rw in the code and see that its value is 4 (as expected).
Pressing
F8 once more brings us to the second part of that same
If statement (because with the values in the sheet the first part of that statement is TRUE) and pressing
F8 again now activates the 2nd row on the sheet. Perfect.
We don't click on anything on the sheet, we leave things exactly as the code left them.
We're now going to run through the rest of the code in one fell swoop by pressing on
F5 on the keyboard and we'll make a note of what's selected when it's finished.
Do it now.
We end up with row 9 being selected.
Now we'll run the whole macro again, but before doing so, we'll select cell E20 on the sheet.
Now click somewhere in the code and press
F5 on the keyboard to run the whole macro at once.
What row is selected now? (It should be row 28.) Not useful.
Hopefully, this shows the unreliablity of depending on the
ActiveCell.
You very, very rarely need to select/activate anything in code. I use it temporarily, during development, to give me a good visual clue/confirmation that my code is acting on the correct cells. You can see an example of this in my code in msg#6:
Code:
'Range("A" & rw + 1 & ":M190").Select ' just shows you which rows will be sorted (no headers)
Currently it is commented-out (inactivated) by the leading apostrophe.
To remove our dependence on what happens to be selected/activated on the sheet, instead of the likes of:
ActiveCell.EntireRow.Offset(1, 0)
we can use something along the lines of:
.cells(rw,5).EntireRow.Offset(1, 0)
Note that I haven't put anything after these code lines because we haven't decided what you're going to do with them, so for the moment we'll just select them so that we can see what the code is looking at:
.Cells(rw, 5).EntireRow.Offset(1, 0).Select
which can be shortened if you want to:
.Cells(rw+1, 5).EntireRow.Select
I've adjusted your code in the macro
sequenceOptimizer2. Step through it with
F8 while observing what changes on the sheet. Note that it no longer matters what is selected before the macro is run, it behaves in exactly the same way. We've lost that dependence on the active cell.
Moving onto
.UsedRange.
It's 9 out of 10 times reliable because very commonly the used range on a sheet includes row 1, the very top row of the sheet, but it ain't always so, so let's look at the sheet
UsedRange2 in the attached.
With that sheet active, we'll step through the macro
sequenceOptimizer2 with
F8:
Just after executing the line:
LastRow = .UsedRange.Rows.Count
hovering over
LastRow shows it has a value of 6. Yes, because the
usedrange on this sheet (as long as you haven't added anything to it) is E9:L14 which only has 6 rows.
So when we come to the next line:
For rw = 4 To LastRow
that translates to:
For rw = 4 To 6
and when you step through the rest of the code, the code is looking at cells which don't even have any values in.
There are various ways to determine what cells/rows code should operate on, but which one you choose depends a lot on what's likely to be on the sheet.
On the current sheet, using the keyboard, you could do the following:
Select cell E9 (top left cell of the range), then press
End, followed by the
down arrow on the keyboard. We end up on cell E14. We can do that in code too. But wait, what if, say, cell E11 is blank? We only end up on cell E10. Not good.
Instead, let's try selecting the cell in the very bottom of the sheet in column E (cell E1048576), pressing
End, then the
up arrrow. We end up on cell E14. Great. Except if there's something below the bottom of the table we want to work on.
OK, let's try selecting a cell within the table, pressing
F5 on the keyboard (or
Ctrl+G) to bring up the
Go To dialogue box, click on
Special… then double-click on the
Current region option. That gives us a range we can work on - as long as we can guarantee that there's a completely blank row, and a completely column, around the range (or the edge of the worksheet) you want to work on.
Those three methods coded for:
Code:
With ActiveSheet
'method 1
LastRow = .Range("E9").End(xlDown).Row
'method 2
LastRow = .Range("E1048576").End(xlUp).Row
'or:
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
'method 3
Set myRange = .Range("F10").CurrentRegion
LastRow = myRange.Cells(myRange.Cells.Count).Row
End With
There's loads on the internet; do a search for
Excel vba finding the last row
Some it came up with:
VBA Tutorial: Find the Last Row, Column, or Cell in Excel
Learn 3 VBA methods to find the last row, column, or cell in a worksheet. The layout of your data and blank cells will determine which method to use.
www.excelcampus.com
VBA Last Row | Top 3 Methods to Find the Last Used Row?
In VBA, when we have to find the last row, there are many different methods. The most commonly used method is the End(XLDown) method. Other methods
www.wallstreetmojo.com
How to Find Last Row, Column, and Cell using VBA in Excel
this tutorial shows you how to use VBA find the last row, last column, and the last cell from a worksheet where you have value.
excelchamps.com
Now onto the 3rd of my 2 points.
I noticed you have some comments:
'Processing Start Date: Column E - Column Number: 5
'Processing Not Before: Column K - Column Number: 11
Clearly a reminder to you of what's where on the sheet.
There's another notation for the likes of:
.Cells(rw, 5)
and it's:
.Cells(rw, "E")
but also useful to the coder is the likes of:
StartDate = "E"
NotBefore = "K"
NotAfter = "L"
'then later refer to cells like:
.cells(rw,StartDate)…
.cells(rw,NotBefore)…
which makes for more readable code.
Now you can probably tell it's taken me some time to produce this guff and it's barely addressed the real
raison d'etre of this thread. I can't continue to act as tutor in this way.
You/We/I need to get back to dealing with your problem. So could you answer my chuffing questions?