Help! Need help to correct and undetermined Range to avoid #N/A's

[liferg]

I had recorded a macro and the given range was ("P2:P3247"), however this number can vary from day to day, higher or lower. If the number is lower then it fills in the #N/A's, and if it goes over then I lose data for the remainder of the macro. I have extended it out to 5000 just to make sure I get all of the data if it goes over, but then I'm stuck with a lot more of the #N/A's. I attempted to use a code from a google search and now I can't get it back to the way it was. Can someone please provide me with some assistance on this? I have a report that needs to be done by 2:00.

 LastRow = Cells(Cells.Rows.Count, "P").End(xlUp).Row
    Range ("P1:P" & LastRow)
    With Range("P2")
        .AutoFill Destination:=Range("P2:P" & InLastRow)
    End With
    With Range("P2:P5000")
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With

 

[liferg]

Not sure how to do that

 

[Eric Golf]

Hi,

I think the problem will be in this line:-

LastRow = Cells(Cells.Rows.Count, "P").End(xlUp).Row

You need to check for last row to fill to in a column different from the one you are filling.

Which column determines

"however this number can vary from day to day"

& replace "P" with that column.

Hope this helps,

Eric.

 

[liferg]

Thank you Eric, that worked.