Auto-Fill Down - LAST ROW problems

S

Sphinx404

Board Regular
Joined
May 2, 2015
Messages
186
Office Version
  1. 365
Platform
  1. Windows
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

Code: 
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]")
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
One option is
Code: 
    [COLOR=#0000ff]Dim Usdrws As Long[/COLOR]

    ActiveWorkbook.Sheets("HazShipper").Select
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    [COLOR=#0000ff]Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/COLOR]

    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=#0000ff] & Usdrws[/COLOR])
This will find the last used row regardless of which column it's in.
 
Upvote 0
There are different methods to determine the last row, depending on which last row you need to use. If you want the absolute last row for any cell that is not blank then
Code: 
lastRow = ActiveSheet.Cells.Find("*", , LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
This will find the first cell with any formula or value in any row or column from the bottom up, which will be the last row of the data base.
But if you only need the last row for a specific column of data, including formulas, then
Code: 
lastRow = ActiveSheet.Cells(Rows.Count, 1),End(xlUp).Row
Where column A is the target column. To change columns, simply substitute the appropriate columm number (or "letter") for the 1.

to use the variable, once you have it initialized,
Code: 
Dim rng As Range
Set rng = Range("A2:A" & lastRow)
Would set the rng variable to a range of cells in column A.
 
Last edited:
Upvote 0
Okay, these are the fixes I'm looking for. @Fluff, your solution above was perfect. @JLGWhiz I've begun to incorporate your examples but my confidence in what I am doing is just causing me to question everything; Because I know what I am doing won't work... per the code below, I know that won't work.

In many of my macros I end up looking in 2 different sheets. @Fluff solution above will always work perfectly for the one sheet. However, many of my macros will end up looking in 2 different sheets and return multiple values.

Lookup Range "U2" (from HazShipper sheet) in DGbyFLT until the last row.
Return data found in column 26 (DGbyFLT) in column AE2 (HazShipper) until the last row.

I guess what I'm asking.

How do I account for the last row in both sheets?

Code: 
Dim Usdrws As Long
[COLOR=#008000]'Dim lastrow As Long????[/COLOR]


ActiveWorkbook.Sheets("[B]HazShipper[/B]").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
[COLOR=#008000]'lastrow = ActiveSheet.Cells(Rows.Count, 1),End(xlUp).Row 'But where do I input????[/COLOR]


Range("AD2").Value = "=TRIM(CLEAN(C2))"
Range("AE2").Value = "=IFNA(VLOOKUP(TRIM(U2),[B]DGbyFLT[/B]!$A$2:$Z$[COLOR=#ff0000]50000[/COLOR],26,0, [COLOR=#ff0000][B]& lastrow[/B][/COLOR]),""No ID"")" [COLOR=#008000]'<---- This obviously isn't going to work.
'actual last row in DGbyFLT is 26978[/COLOR]
Range("AF2").Value = "=IFNA(IF(AD2=AE2,TRUE,FALSE),""No Match"")"


Range("AD2:AF2").Select
Selection.AutoFill Destination:=Range("AD2:AF" & Usdrws)

Thank you both for taking the time to help.
 
Upvote 0
Try
Code: 
Dim Usdrws As Long
Dim lastrow As Long


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


Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastrow = Sheets("DGbyFLT").Cells(Rows.Count, 1).End(xlUp).Row


Range("AD2").Value = "=TRIM(CLEAN(C2))"
Range("AE2").Value = "=IFNA(VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & lastrow & ",26,0,),""No ID"")"
'actual last row in DGbyFLT is 26978
Range("AF2").Value = "=IFNA(IF(AD2=AE2,TRUE,FALSE),""No Match"")"


Range("AD2:AF2").Select
Selection.AutoFill Destination:=Range("AD2:AF" & Usdrws)
 
Upvote 0
You just need to specify the sheets in both the variablename (for future reference) and in the cells command

HazShipperUsdRws = Sheets("HazShipper").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

DGbyFLTUsdRws = Sheets("DGbyFLT").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
 
Upvote 0
@Fluff,

I've used your solution above and it also worked flawlessly. I have a similar set of code that isn't working so well... quite peculiar actually.

As I mentioned before, the HazShipper sheet is only 1947 rows of data. The code below should only return data in the 1947 rows, however, it populates data in 21,947 rows.... 20K extra.. every time. I checked all the code before this one and nothing populates in any cell below 1947, and I even cleared all rows below 1947 just to make sure it wasn't attaching itself to some phantom data.... but still... the code below always adds on the extra 20K rows.

ideas? Also, didn't use "lastrow" on this one... not yet anyway.

ENTIRE CODE:
Code: 
Sub Step4()
[COLOR=#008000]'sub looks compares destination to customer ID, TRUE if match, false if no match[/COLOR]


Dim Usdrws As Long
[COLOR=#008000]'Dim lastrow As Long[/COLOR]


    ActiveWorkbook.Sheets("HazShipper").Select
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
[COLOR=#008000]'lastrow = Sheets("AirportCodes").Cells(Rows.Count, 1).End(xlUp).row[/COLOR]


    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" & Usdrws)

[COLOR=#008000]'organize columns[/COLOR]
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "Customer ID"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "HazShipper Destination"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "Airport Code Destination"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "Dest. Match?"
    Range("Z1:AC1").Select
    Selection.Font.Bold = True
    Range("Z1,AA1").Select
    With Selection.Interior
        .Color = 65535
    End With
    Columns("Z:Z").ColumnWidth = 8.86
    Columns("AA:AC").EntireColumn.AutoFit
    Columns("Z:AC").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Columns("AC:AC").ColumnWidth = 6.86
    Range("AD2").Select


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub
 
Last edited:
Upvote 0
Try deleting all rows below row 1947 rather than clearing them, then save & close the workbook.
Reopen it & try again.
Does this make any difference?
 
Upvote 0
Jonmo1 said:
You just need to specify the sheets in both the variablename (for future reference) and in the cells command

HazShipperUsdRws = Sheets("HazShipper").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

DGbyFLTUsdRws = Sheets("DGbyFLT").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Click to expand...

This solution seems a lot simpler than I... How do I say this.... My brain is dumb and the coding makes sense... However, I am a hands on learner... so I I'll need to incorporate this into my code in a number of ways before my brain grasps what you're actually telling me.

Thanks @Jonmo1 It's appreciated.
 
Upvote 0
Nothing... Did as you have stated. Also, ran the macro by itself rather than running it as I have it laid out.... (steps... this is step 4)

There are no values in any cell below row 1947. I don't get it...


All code leading up to step4
Code: 
[COLOR=#ff0000]Sub Step1()[/COLOR]
'Encompasses Step1 as per Takalsky's outline




'this procedure determines Airway Bill, sorting the AWB based on length
ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim mycell As Range


For Each mycell In Range("E2", Range("E" & Rows.Count).End(xlUp))


If Len(mycell) = 14 Then mycell.Offset(, 16).Value = Right(mycell.Value, 8)


If Len(mycell) = 21 Then mycell.Offset(, 16).Value = "UPS"


If Len(mycell) = 22 Then mycell.Offset(, 16).Value = "UPS"


If Len(mycell) <= 7 Then mycell.Offset(, 16).Value = "Analyze"


If Len(mycell) = 9 Then mycell.Offset(, 16).Value = "Unknown"


If Len(mycell) >= 23 Then mycell.Offset(, 16).Value = "Analyze"


Next mycell


'this procedure extracts the AWB based on CASE.  Primarily looks at 3rd Party shippers and not the AWB#
For Each mycell In Range("E2", Range("E" & Rows.Count).End(xlUp))


Select Case True
    Case Left(mycell.Value, 2) = "06"
        If IsNumeric(Right(mycell.Value, 8)) Then
            mycell.Offset(, 16).Value = Right(mycell.Value, 8)
        End If
    Case UCase(mycell.Value) Like "1Z*" Or mycell.Value Like "UPS*"
            mycell.Offset(, 16).Value = "UPS"
    Case mycell.Value Like "*FED*" Or mycell.Value Like "FEDEX"
            mycell.Offset(, 16).Value = "FEDEX"
    Case mycell.Value Like "*DHL*"
            mycell.Offset(, 16).Value = "DHL"
    Case mycell.Value Like "EXPO*"
            mycell.Offset(, 16).Value = "EXPO"
    Case mycell.Value Like "STERL*"
            mycell.Offset(, 16).Value = "STERLING"
    Case UCase(mycell.Value) Like "CHART*"
            mycell.Offset(, 16).Value = "CHARTER"
    Case mycell.Value Like "*TRUCK*"
            mycell.Offset(, 16).Value = "TRUCK"
    Case mycell.Value Like "*-*"
            mycell.Offset(, 16).Value = "Analyze"
            
End Select


Next mycell


'this procedure locates the blank cells and inputs "research".  Also dresses up the column.
Dim Usdrws As Long


Usdrws = Cells.Find("*", after:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    Range("U2:U" & Usdrws).SpecialCells(xlBlanks).Value = "Analyze"
    Range("U1").Select
        ActiveCell.FormulaR1C1 = "Actual Airway Bill #"
        Selection.Font.Bold = True
    Columns("U:U").Select
    Selection.ColumnWidth = 11.14
    Columns("U:U").Select
        With Selection
            .HorizontalAlignment = xlCenter
        End With
    Range("V2").Select


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub
[COLOR=#ff0000]Sub Step2()[/COLOR]
'THIS SUB: Pulls the Airport code from the original Airway Bill


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


Dim mycell As Range


For Each mycell In Range("E2", Range("E" & Rows.Count).End(xlUp))


Select Case True
    Case UCase(mycell.Value) Like "*ATL*"
            mycell.Offset(, 17).Value = "ATG"
    Case UCase(mycell.Value) Like "*MSP*"
            mycell.Offset(, 17).Value = "MSP"
    Case UCase(mycell.Value) Like "*DTW*"
            mycell.Offset(, 17).Value = "DTW"
    Case mycell.Value = ""
            mycell.Offset(, 17).Value = "No AWB"
    Case Else
            mycell.Offset(, 17).Value = "N/A"
End Select


Next mycell


Range("V1").Select
    ActiveCell.FormulaR1C1 = "AWB Airport Code"
    Selection.Font.Bold = True
    Columns("V:V").Select
    Columns("V:V").EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlCenter
    End With




'THIS SUB: Compares data in Column A with column V.  If column V is *LIKE* column A then TRUE.
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(CLEAN(RC[-19]))"
    Range("T2").Select
    Selection.AutoFill Destination:=Range("T2:T25000")
    Range("T2:T25000").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("T1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Column A Values"
    Range("T1").Select
    Selection.Font.Bold = True
    With Selection
        .WrapText = True
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Columns("T:T").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With


Dim myrange As Range


For Each myrange In Range("A2", Range("A" & Rows.Count).End(xlUp))


Select Case UCase(Left(myrange.Offset(, 21), 3))
    Case "ATG"
        If myrange.Value Like "AT*" Then
            myrange.Offset(, 22).Value = "True"
        Else
            myrange.Offset(, 22).Value = "False"
        End If
    Case "MSP"
        If myrange.Value Like "MSP*" Then
            myrange.Offset(, 22).Value = "True"
        Else
            myrange.Offset(, 22).Value = "False"
        End If
    Case "DTW"
        If myrange.Value Like "DTW*" Then
            myrange.Offset(, 22).Value = "True"
        Else
            myrange.Offset(, 22).Value = "False"
        End If
    Case "N/A"
        If myrange.Offset(, 21).Value Like "N/A" Then
            myrange.Offset(, 22).Value = "N/A"
        Else
            myrange.Offset(, 22).Value = "False"
        End If
    Case Else
            myrange.Offset(, 22).Value = "No AWB"
End Select


Next myrange


Range("W1").Select
        ActiveCell.FormulaR1C1 = "AWB Airport Match Column A?"
        Selection.Font.Bold = True
    Columns("W:W").Select
    Columns("W:W").EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Range("X2").Select
    Columns("T:T").ColumnWidth = 10.14


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub
[COLOR=#ff0000]Sub Step3()[/COLOR]
'THIS SUB:  Compares the first 5 characters in Column J(designated by the macro) with Column A.


Dim Usdrws As Long


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


Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row


Range("X2").Value = "=TRIM(CLEAN(J2))"
Range("X2").Select
    Selection.AutoFill Destination:=Range("X2:X" & Usdrws)
        


Dim myrange As Range


For Each myrange In Range("A2", Range("A" & Rows.Count).End(xlUp))


    Select Case UCase(Left(myrange.Offset(, 9), 5))
        Case "MINNE"
            If myrange.Value Like "*MSP*" Then
                myrange.Offset(, 24) = "True"
            End If
        Case "ST.PA"
            If myrange.Value Like "*MSP*" Then
                myrange.Offset(, 24) = "True"
            End If
        Case "ATLAN"
            If myrange.Value Like "AT*" Then
                myrange.Offset(, 24) = "True"
            End If
        Case "DETRO"
            If myrange.Value Like "DTW*" Then
                myrange.Offset(, 24) = "True"
            End If
        Case ""
            If myrange.Offset(, 9).Value = "" Then
                myrange.Offset(, 24) = "No Departure"
            End If
        Case Else
                myrange.Offset(, 24) = "False"
    End Select
Next


    Range("X1").Select
    ActiveCell.FormulaR1C1 = "Departure Airport"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "Departure Match Column A"
    Range("X1:Y1").Select
    Selection.Font.Bold = True
    Columns("X:Y").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Columns("X:Y").EntireColumn.AutoFit
    Range("X1").Select
    With Selection.Interior
        .Color = 65535
    End With
    Range("Z2").Select


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub
[COLOR=#ff0000]Sub Step4()[/COLOR]
'sub looks compares destination to customer ID, TRUE if match, false if no match


Dim Usdrws As Long


    ActiveWorkbook.Sheets("HazShipper").Select
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row


    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" & Usdrws)


    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "Customer ID"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "HazShipper Destination"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "Airport Code Destination"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "Dest. Match?"
    Range("Z1:AC1").Select
    Selection.Font.Bold = True
    Range("Z1,AA1").Select
    With Selection.Interior
        .Color = 65535
    End With
    Columns("Z:Z").ColumnWidth = 8.86
    Columns("AA:AC").EntireColumn.AutoFit
    Columns("Z:AC").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Columns("AC:AC").ColumnWidth = 6.86
    Range("AD2").Select


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Office Script for auto fill to last row of adjacent column
Replies
6
Views
342
ExcelToDAX
E
N
  • Solved
Find next visible cell, add a formula and then fill column down
Replies
8
Views
356
MARK858
MARK858
I
  • Question Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
465
ItalianPlatinum
I
S
  • Solved
Copy row 2 and paste it after last row of another worksheet
Replies
9
Views
1K
rollis13
rollis13
MustacheKaboom
  • Solved
VBA Noob. All but the last step works... GAH!!
Replies
4
Views
540
MustacheKaboom
MustacheKaboom

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top