Finding last row of data

[SammyCRX]

Hi,

I use the same bit of opening code on just about everything I ever work on (I know there are a lot of different ways of doing this):

'Finds last row of data on the Altos billing Nov 18 calls    Sheets("Altos billing Nov 18 calls").Select
    LastRowWithValueAltos = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row
'
    Sheets("Altos billing Nov 18 calls").Select
' Adds headers
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Mapping"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Lead Reseller"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Site ID"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Mapping2"
    
' Adds formulas
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=RC[-8]&RC[-2]"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Mapping!C[-12]:C[-8],2,FALSE)"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Mapping!C[-13]:C[-9],5,FALSE)"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=RC[-11]&RC[-2]&RC[-7]"
    Range("L2:O2").Select
    Selection.Copy
    Range("L3:N" & LastRowWithValueAltos).Select
    ActiveSheet.Paste

For some reason, 'Range("L3:N" & LastRowWithValueAltos).Select' is causing it to copy only for L3:N3, which leads me to believe LastRowWithValueAltos is returning a value of 3. However, cells in column A are populated with numbers all the way to 52,206, so I can't think why it's not returning that value. Have I just missed something really obvious here? Like I mentioned, I use this approach all the time, so I think I must just be missing something really obvious.

I'm changing the headers bit to an array at a later stage by the way (most of this was recorded, so I tidy up at the end using what little knowledge I have)

Thanks,
Sam

 

[MARK858]

What number do you get with with

Sub zzzz()
MsgBox Sheets("Altos billing Nov 18 calls").Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row
End Sub

 

[SammyCRX]

Hi,

Thanks for the reply. I tried that and it returned 52,206...

Thanks,
Sam

 

[footoo]

Range("L2:O2").Select
    Selection.Copy
    Range("L3:N" & LastRowWithValueAltos).Select
    ActiveSheet.Paste

Your destination range has a different numbers of columns - try "L3:O"

 

[MARK858]

What happens with the below?

Sub xxxx()
    Dim LastRowWithValueAltos As Long
    With Sheets("Altos billing Nov 18 calls")
        LastRowWithValueAltos = .Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row
        ' Adds headers
        .Range("L1").Value = "Mapping"
        .Range("M1").Value = "Lead Reseller"
        .Range("N1").Value = "Site ID"
        .Range("O1").Value = "Mapping2"

        ' Adds formulas
        .Range("L2").FormulaR1C1 = "=RC[-8]&RC[-2]"
        .Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-1],Mapping!C[-12]:C[-8],2,FALSE)"
        .Range("N2").FormulaR1C1 = "=VLOOKUP(RC[-2],Mapping!C[-13]:C[-9],5,FALSE)"
        .Range("O2").FormulaR1C1 = "=RC[-11]&RC[-2]&RC[-7]"
        .Range("L2:O" & LastRowWithValueAltos).FillDown
    End With
End Sub

or

Sub xxxx()
    Dim LastRowWithValueAltos As Long
    With Sheets("Altos billing Nov 18 calls")
        LastRowWithValueAltos = .Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row
        ' Adds headers
        .Range("L1").Value = "Mapping"
        .Range("M1").Value = "Lead Reseller"
        .Range("N1").Value = "Site ID"
        .Range("O1").Value = "Mapping2"

        ' Adds formulas
        .Range("L2").FormulaR1C1 = "=RC[-8]&RC[-2]"
        .Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-1],Mapping!C[-12]:C[-8],2,FALSE)"
        .Range("N2").FormulaR1C1 = "=VLOOKUP(RC[-2],Mapping!C[-13]:C[-9],5,FALSE)"
        .Range("O2").FormulaR1C1 = "=RC[-11]&RC[-2]&RC[-7]"
        .Range("L2:O2").Copy .Range("L3:O" & LastRowWithValueAltos)
    End With
End Sub

 

[SammyCRX]

Range("L2:O2").Select
    Selection.Copy
    Range("L3:N" & LastRowWithValueAltos).Select
    ActiveSheet.Paste

Your destination range has a different numbers of columns - try "L3:O"

Thanks... I can't believe I missed that... I'm such an idiot! It's where I realised we needed an extra column of formulas, but I completely forgot to change the next row. Thanks

 

[SammyCRX]

What happens with the below?

Sub xxxx()
    Dim LastRowWithValueAltos As Long
    With Sheets("Altos billing Nov 18 calls")
        LastRowWithValueAltos = .Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row
        ' Adds headers
        .Range("L1").Value = "Mapping"
        .Range("M1").Value = "Lead Reseller"
        .Range("N1").Value = "Site ID"
        .Range("O1").Value = "Mapping2"

        ' Adds formulas
        .Range("L2").FormulaR1C1 = "=RC[-8]&RC[-2]"
        .Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-1],Mapping!C[-12]:C[-8],2,FALSE)"
        .Range("N2").FormulaR1C1 = "=VLOOKUP(RC[-2],Mapping!C[-13]:C[-9],5,FALSE)"
        .Range("O2").FormulaR1C1 = "=RC[-11]&RC[-2]&RC[-7]"
        .Range("L2:O" & LastRowWithValueAltos).FillDown
    End With
End Sub

Thanks for your help... problem solved! I was thinking some sort of weird formatting issue or something else I hadn't seen before, but it was just a difference in column numbers. I'm blaming it on being a Friday :D

Thanks,
Sam