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.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
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 "..."
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,945
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
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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.
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
So is there a way to make the formula paste in Column B to match column A?
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
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.
 

ERed1

Board Regular
Joined
Jun 26, 2019
Messages
104
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!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
You are welcome!
:)
 

Forum statistics

Threads
1,082,589
Messages
5,366,494
Members
400,895
Latest member
Anthonemr

Some videos you may like

This Week's Hot Topics

Top