Dynamic Range For top and bottom

[Felix1980]

Hello,
I'm trying to autofill some cells but I do not want to autofill from the top of the column I need to only fill from the first empty cell in column C down to the last used cell in column A. I've tried several different iterations the following is currently what I've attempted but it is not working. Any help is appreciated.

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

 

[Fluff]

How about

With Range("C" & Rows.Count).End(xlUp)
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 2))
End With

 

[Mentor82]

Hi,
Trythe following code .

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

 

[Felix1980]

Hello,
Thank you for the quick replies! I've tried both of your suggestions. I notice that they grab the info from the last used cell in C range for the data they are copying down. Is it possible for it to grab the formula from C2 to drag down the column starting down at the bottom of the C column used range to bottom of column A used range? The reason for this is sometimes the user may have to manually adjust the last entry effectively removing the formula from the last used line, then that manual entry copies down instead of the formula. Also, these codes are working in a module but not in my userform, would anyone know why?

 

[Fluff]

ADVERTISEMENT

Try

With Range("C" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("C3").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 2))
End With

When using this in the userform, is the active sheet the one you want this to work on?

 

[Mentor82]

Hi,
Try this instead. In this case the first row is no longer needed because the autofill in is supposed to be done from the first cell of the range to the last.
Let me know if thats OK for you.

e: [View]
    Dim lastRow As Long
   ' Dim FirstLastRow As Long
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
   ' FirstLastRow = Range("C" & Rows.Count).End(xlUp).Row
    
    Range("C2").AutoFill Destination:=Range("C2", "C" & lastRow)

 

[Fluff]

ADVERTISEMENT

@Mentor82
That will remove the values that will remove the manually entered values, which I suspect the OP want's to keep.

 

[Felix1980]

Thank you Fluff! That worked! Yes, the userform is activated on the same sheet it adds the data. Thank you again!

 

[Mentor82]

@Fluff - I fully agree with you but I understood so.
@Felix1980 - try below code instead.

    Dim lastRow As Long
    Dim FirstLastRow As Long
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    FirstLastRow = Range("C" & Rows.Count).End(xlUp).Row
Range("C2"). Copy Destination=:Range("C" & FirstLastRow+1)
    
    Range("C" & FirstLastRow+1).AutoFill Destination:=Range("C" & FirstLastRow+1, "C" & lastRow)
[\CODE]

 

[Fluff]

Thank you Fluff! That worked! Yes, the userform is activated on the same sheet it adds the data. Thank you again!

In that case can you please supply the entire procedure that this is part of & explain what is or isn't happening.