VBA Code to Autofill value found in LAST filled cell

Kristylee0228

New Member
Joined
Sep 8, 2011
Messages
30
Hi there,
I have a Macro that finds the last empty cell in a column and fills it with the value "CO".
lastRow = Cells(Rows.Count, 3).End(xlUp).Row
Cells(lastRow + 1, 3).Select
ActiveCell.FormulaR1C1 = "CO"
ActiveCell.Select

I now need the Value "CO" to Autofill from this point until end of the worksheet.
This is what I thought to do, but it does not work. Also, I don't want the "CO" to fill anything above the last empty cell.
'Selction.AutoFill Destination:=Range("C2:C" & lastRow)
'Selection.Copy
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
':=False, Transpose:=False
'Application.CutCopyMode = False

Any help would be greatly appreciated!!!
Thank you,
Kristy
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
not sure if I quite understand But I think this what you want.

try

VBA Code:
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
Cells(lastrow + 1, 3) = "CO"
Range("C" & lastrow + 1).Resize(50 - lastrow) = "CO" 'if end of workbook is row 50
Application.CutCopyMode = False
 
Upvote 0
not sure if I quite understand But I think this what you want.

try

VBA Code:
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
Cells(lastrow + 1, 3) = "CO"
Range("C" & lastrow + 1).Resize(50 - lastrow) = "CO" 'if end of workbook is row 50
Application.CutCopyMode = False
Thank you for the response. The end of the workbook varies each day depending on number of people working.
I found this .. using FillDown and it works perfectly!!

n = Cells(Rows.Count, "A").End(xlUp).Row 'last filled cell in A
lastRow = Cells(Rows.Count, 3).End(xlUp).Row 'last filled cell in C
Range("C" & lastRow & ":C" & n).FillDown
 
Upvote 0
Solution
What about simply filling them all together in the first place rather putting one "CO" in and then filling down?
I have also put a check in just in case it was possible that column C was already as long or longer than column A

VBA Code:
n = Cells(Rows.Count, "A").End(xlUp).Row 'last filled cell in A
lastrow = Cells(Rows.Count, 3).End(xlUp).Row 'last filled cell in C
If n > lastrow Then Range("C" & lastrow + 1 & ":C" & n).Value = "CO" 'fill all with CO
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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