Auto-Fill Down - LAST ROW problems

[Sphinx404]

There are literally hundreds of posts on this forum that have to do with last row, I've run a few of the solutions and for some reason or another I just get error after error.

Many of the macros/steps in my workbook are set to a specific point (highlighted below) because I have no idea how to run a "last row" procedure that will actually work. I've been able to learn a great deal about VBA through this macro building process and this last row variable is putting a hitch in my giddy-up.

i.e. when filtering the final data, it shows rows that don't matter because I've run the code to a specific spot (highlighted below). This doesn't make sense to do and I've been avoiding it until now...

Could an expert please help me through a couple of my coding problems and work through a few solutions?

Problem #1:

Need to fill down results to last row. Actual last row in the particular report will vary, but currently last row = 1947

ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Range("Z2").Value = "=LEFT(TRIM(CLEAN(Q2)),3)"
Range("AA2").Value = "=TRIM(CLEAN(K2))"
Range("AB2").Value = "=IFNA(VLOOKUP(Z2,AirportCodes!$A$2:$C$2000,2,0),""No Departure"")"
Range("AC2").Value = "=IF(LEFT(K2,4)=LEFT(AB2,4),True,False)"


Range("Z2:AC2").Select
Selection.AutoFill Destination:=Range("Z2:AC[COLOR=#ff0000][B]25000[/B][/COLOR]")

 

[Sphinx404]

You can find copies of the files I use here:

https://infinitepost.com/2017/11/27/macros/

 

[Fluff]

This is one culprit, there may be more

 Selection.AutoFill Destination:=Range("T2:T[COLOR=#ff0000]25000[/COLOR]")

Your filling a formula down to row 25000

 

[Sphinx404]

I thought I had accounted for all of these prior to step 4.... Let me go through line by line and use your solutions for getting to the last row....

 

[Sphinx404]

That's a negative.... Even after fixing the 25000 fill down it still adds 20K rows... I'm going to keep chipping away at it... maybe I can find the discrepancy. Many people need your expertise @Fluff I don't want to pull you away on my mediocre BS. Thanks a bunch for your help.

 

[Fluff]

Because you've already filled those cells, you'll need to go through the rigmarole in post#8 again.

 

[Sphinx404]

Because you've already filled those cells, you'll need to go through the rigmarole in post#8 again.

@Fluff

I started from scratch today, I also took an online course as it refers to "last row" and the various ways of manipulating the code and the consequences of using the different methods of attaining the last row. With that being said, I've gone back through my code and took your suggestions from yesterday. The code hasn't changed really but now that I know what I'm looking at I can tell you for certain... hovering over "myLastRow per the code below. It says 1947.

So one would assume that the Range selected would AutoFill per the last row: 1947. But yet, it AutoFills down exactly 20,000 extra rows. Not 19,451, not 5,587 but exactly 20,000 rows. I've ruled out formatting issues that I might not be seeing...I'm beginning to wonder if there is something in this report (which comes from SAP) that I just can't see.

Dim myLastRow As Long
Dim myWorksheet As Worksheet


Set myWorksheet = Worksheets("HazShipper")


myLastRow = myWorksheet.Cells(myWorksheet.Rows.Count, "A").End(xlUp).row


ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    
    
    Range("Z2").Value = "=LEFT(TRIM(CLEAN(Q2)),3)"
    Range("AA2").Value = "=TRIM(CLEAN(K2))"
    Range("AB2").Value = "=IFNA(VLOOKUP(Z2,AirportCodes!$A$2:$C$2000,2,0),""No Departure"")"
    Range("AC2").Value = "=IF(LEFT(K2,4)=LEFT(AB2,4),True,False)"
        
    Range("Z2:AC2").Select
    Selection.AutoFill Destination:=Range("Z2:AC2" & myLastRow)

EDIT* - I ran this in a new WB and it runs fine. Something is wrong with the sheet.

 

[Fluff]

The last line should be

Selection.AutoFill Destination:=Range("Z2:AC" & myLastRow)

You are concatenating the range address so AC2 & lastmyrow is AC2 & 1947 ie AC21947

 

[Sphinx404]

Wow.

I didn't know that was a problem.

In my mind... if you want columns between A thru Z then it's A:Z, and in that thinking I figured if I want lines Z2 through AC2 then Z2:AC2

I'll have to look up the reasoning behind it... my brain interprets that as wrong.

I'm glad I have this in my knowledge bucket now. Thank you @Fluff

EDIT* - Ahhhhh.... Z2 & AC (last row, i.e. 1947).... I'm not thinking of it as a range.

 

[Fluff]

In my mind... if you want columns between A thru Z then it's A:Z, and in that thinking I figured if I want lines Z2 through AC2 then Z2:AC2

Your thinking is correct.
It's when you use a variable (ie lastrow) if lastrow is 2 then Z2:AC2 is the same as Z2:AC & lastrow. But Z2:AC2 & lastrow is Z2:AC22