Dynamic Range For top and bottom

Felix1980

New Member
Joined
May 16, 2018
Messages
40
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.

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("C2").AutoFill Destination:=Range(FirstLastRow, lastRow)
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
How about
Code:
With Range("C" & Rows.Count).End(xlUp)
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 2))
End With
 
Upvote 0
Hi,
Trythe following code :).

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)
 
Upvote 0
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?
 
Upvote 0
Try
Code:
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?
 
Upvote 0
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.
Code:
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)
 
Upvote 0
@Mentor82
That will remove the values that will remove the manually entered values, which I suspect the OP want's to keep.
 
Upvote 0
Thank you Fluff! That worked! Yes, the userform is activated on the same sheet it adds the data. Thank you again!
 
Upvote 0
@Fluff - I fully agree with you but I understood so.
@Felix1980 - try below code instead.
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("C2"). Copy Destination=:Range("C" & FirstLastRow+1)
    
    Range("C" & FirstLastRow+1).AutoFill Destination:=Range("C" & FirstLastRow+1, "C" & lastRow)
[\CODE]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top