Copy of last cell until last cell in another column

[Kruk07]

Hi

I'm working on macro that allow me to copy last cell in column A to all empty rows after that cell in column A until last used raw in column C.

I have tried few but non of them are working.

I have userform that fills the sheet with data but one listbox is multiple selection and in column C I have more lines of entries than in other columns so the gaps needs to be populated with the data in the last row used in column A

    Dim lastRow As Long
    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    Range("A2").AutoFill Destination:=Range("A2:A" & lastRow)

Thanks

Tom

 

[Fluff]

How about

   Range(Range("A" & Rows.Count).End(xlUp), Range("C" & Rows.Count).End(xlUp).Offset(, -2)).Filldown

 

[Kruk07]

Thanks

This works great. However when I have only one selection in listbox macro is copy the line one before the last one. Any fix for this?

Thanks

 

[Fluff]

How about

   Dim LrA As Long, LrC As Long
   LrA = Range("A" & Rows.Count).End(xlUp).Row
   LrC = Range("C" & Rows.Count).End(xlUp).Row
   If LrC > LrA Then Range("A" & LrA & ":A" & LrC).Filldown

 

[Kruk07]

Perfect. Many thanks.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Rick Rothstein]

How about

   Dim LrA As Long, LrC As Long
   LrA = Range("A" & Rows.Count).End(xlUp).Row
   LrC = Range("C" & Rows.Count).End(xlUp).Row
   If LrC > LrA Then Range("A" & LrA & ":A" & LrC).Filldown

I am obviously missing something, but I do not see where this code produces any different result than your original one-liner in Message #2 did. Why was it necessary to make this change?

 

[Fluff]

If the A5 is the last cell in col A & C5 the last in col C, then the original code will fill A4 into A5

 

[Rick Rothstein]

If the A5 is the last cell in col A & C5 the last in col C, then the original code will fill A4 into A5

Ah, I see (I had used a bad set of data values when I tested your code ). Here is another way to write the code...

With Range(Cells(Rows.Count, "A").End(xlUp), Cells(Rows.Count, "C").End(xlUp).Offset(, -2))
  .Value = .Cells(1).Value
End With