CODEWITHSUNDEEP
vbaexcel
MarioS
MarioS

Reputation: 262

Dynamic Last Row

I'm using the following code to find the last row in Sheet1 & Sheet2

Dim 1LastRow As Long
Dim 2LastRow As Long
1LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
2LastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

This works as initially expected, however I'd like "1LastRow" to adjust to dynamically pick up the last row.

For example, if Sheet1 initially has 50 rows, but after:

Sheets("Sheet2").Range("B2:B" & 2LastRow).Copy
Sheets("Sheet1").Range("D2").PasteSpecial xlPasteValues

Sheet1 now has 55 rows. However, "1LastRow" is still assuming that Sheet1 only has 50 rows, and thus creates inconsistencies throughout the rest of the code when "1LastRow" is used. Any input is greatly appreciated!

Upvotes: 0

Views: 4521

Answers (2)

teylyn
teylyn

Reputation: 35915

Well, a variable doesn't magically update itself. You see that you have code that pastes rows. Just repeat the code that sets the variable for the last row when you do something that changes the last row.

Dim 1LastRow As Long
Dim 2LastRow As Long
1LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
2LastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet2").Range("B2:B" & 2LastRow).Copy
Sheets("Sheet1").Range("D2").PasteSpecial xlPasteValues 
1LastRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Or you could use a dynamic range name with a formula along the lines of

=INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Then refer to that range like

ThisWorkbook.Worksheets("Sheet1").Range("OneLastRow").Row

But this will fall over if the column contains blank cells.

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

first off your code won't compile in an Office VBA environment: variables names can't start with a number. So let's call them LastRow1 and LastRow2, instead

second, even if there were some automagic way of making a variable self update, your code would fail since you are copying a 1-column range from Sheet2 and pasting it starting from cell D2 of Sheet1, which would have no effect whatsoever in column A last row index setting

all that said, you have could use a Function() that returns the wanted row index.

For instance this function:

Function LastRow1() As Long
    With Sheets("Sheet1")
        LastRow1 = .Cells(.Rows.Count, 4).End(xlUp).Row
    End With
End Function

would return Sheet1 column D last not empty row index (well, it would actually fail should column A be totally empty, but it's an easy fix)

you could easily have it return any other column last not empty cell row index by simply changing column index in .Cells(.Rows.Count, 4)

so that your "main" code would look like:

Sub main()
    Dim LastRow2 As Long
    LastRow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row 

    Sheets("Sheet2").Range("B2:B" & LastRow2).Copy
    Sheets("Sheet1").Range("D2").PasteSpecial xlPasteValues

    MsgBox LastRow1 ' this would return Sheet1 column D last not empty row index

    MsgBox Sheets("Sheet1").Range("D2:D" & LastRow1).Address 'this would return the address of Sheet1 column D range from cell D2 down to last not empty cell

End Sub

Upvotes: 1

Related Questions