lastRow Function

[ERed1]

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]

Hey,

If you're using VBA you can access the last used row in column A with the following line of 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]

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:

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]

If you want it in a UDF it would be something like this which takes the column letter as an argument:

Function LastRow(sColumn As String) As Long

    LastRow = Range(sColumn & Rows.Count).End(xlUp).Row
End Function

 

[ERed1]

So the current macro I am using is this:

    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]

So is there a way to make the formula paste in Column B to match column A?

 

[ERed1]

Nevermind I was able to find the issue. Whenever you go to another sheet, you need to paste

    lastRow = Cells(Rows.Count, 2).End(xlUp).Row

this again in the code when you get to the new sheet.

 

[Joe4]

This:

    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:

    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]

This:

    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:

    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]

You are welcome!