lastRow Function

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
Hi all,

So I have sheet that has data pasted in column A. There is a lot of data. In Column B I have a formula and I want this formula to be pasted in all cells in which A has a value. So for example if column A's last row used is 1000, I want this code to paste the code in B but only to 1000. The 1000 changes frequently, that is why I need a lastRow macro to help it be dynamic.

Thanks in advance for the help.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hey,

If you're using VBA you can access the last used row in column A with the following line of code:
Code:
Cells(Rows.Count, 1).End(xlUp).Row

That will return an integer (lets say 1000 in the example above)
Then you can loop through column B from 1 to 1000 and enter the formula you want.
 
Upvote 0
Then you can loop through column B from 1 to 1000 and enter the formula you want.
Depending on your formula, you probably do not need to loop at all. If you use the R1C1 range reference, you can probably assign all the formulas at once in a single command like this:
Code:
Dim lRow as Long
LRow = [COLOR=#333333]Cells(Rows.Count, 1).End(xlUp).Row[/COLOR]
[COLOR=#ff0000]Range("B1:B" & LRow).FormulaR1C1 = "..."[/COLOR]
If you aren't sure how you need to write the formula to put in the VBA code, all you need to do is to turn on your Macro Recorder, and record yourself manually entering the formula in cell B1.
Then paste that part of the code above where I have "..."
 
Upvote 0
If you want it in a UDF it would be something like this which takes the column letter as an argument:

Code:
Function LastRow(sColumn As String) As Long

    LastRow = Range(sColumn & Rows.Count).End(xlUp).Row
End Function
 
Upvote 0
So the current macro I am using is this:
Code:
    Dim lastRow As Long    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    Range("AL2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],PIVOT!C[-37]:C[-36],2,FALSE),""0"")"
    Selection.AutoFill Destination:=Range("AL2:AL" & lastRow)

I have 2 sheets. On sheet 1 lets say there are 1000 rows in A that are filled. This code works perfectly in filling column B to that point. I then go to sheet 2, which has more than 1000 rows fill in column A, but it only pastes to 1000. So I think it is still reading off of Sheet 1.

Now that I think about it, I should have used a different title and entrance. My bad guys. I originally planned on chaging my "lastRow", but there might be a better way.
 
Upvote 0
Nevermind I was able to find the issue. Whenever you go to another sheet, you need to paste
Code:
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
this again in the code when you get to the new sheet.
 
Upvote 0
This:
Code:
    Dim lastRow As Long    
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    Range("AL2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],PIVOT!C[-37]:C[-36],2,FALSE),""0"")"
    Selection.AutoFill Destination:=Range("AL2:AL" & lastRow)
can be simplified to this:
Code:
    Dim lastRow As Long    
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    Range("AL2:AL" & lastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],PIVOT!C[-37]:C[-36],2,FALSE),""0"")"
No need for Autofill or Select at all.
 
Upvote 0
This:
Code:
    Dim lastRow As Long    
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    Range("AL2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],PIVOT!C[-37]:C[-36],2,FALSE),""0"")"
    Selection.AutoFill Destination:=Range("AL2:AL" & lastRow)
can be simplified to this:
Code:
    Dim lastRow As Long    
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    Range("AL2:AL" & lastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],PIVOT!C[-37]:C[-36],2,FALSE),""0"")"
No need for Autofill or Select at all.
Thanks for the advice!
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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