Excel Macro - Set and autofill a range?

Solved/Closed
KT - Mar 19, 2009 at 05:56 PM
excel987 Posts 3 Registration date Tuesday June 11, 2019 Status Member Last seen June 12, 2019 - Jun 11, 2019 at 11:15 PM
Hello,
I am creating an excel macro to reformat a CSV file. In doing this, I need to select a column (F) and replace the values in that column with another value.

My biggest problem is that I cannot specify the range and then auto-fill it with a value. I need the range to start at F1 and go to the last populated cell.

Here's what I've tried (part of it is from the recorded macro - I am new to this.). I would really appreciate anyone's advice! Thanks.

This is a snippet of the "recorded" macro that works. I basically need to replace the range "F1:F226" with a range that is F1 to the last populated cell.

Range("F1").Select
ActiveCell.FormulaR1C1 = "P"
Range("F1").Select
Selection.AutoFill Destination:=Range("F1:F226"), Type:=xlFillDefault
Range("F1:F226").Select

I tried modifying is to this, but it gives me an error that the "autofill method of Range class failed"

Range("F1").Select
ActiveCell.FormulaR1C1 = "P"
Range("F1").Select
Range("F1", Range("F1").End(xlDown)).Name = "typeColRange"
Selection.AutoFill Destination:=typeColRange, Type:=xlFillDefault

8 replies

Maybe this will help:

Selection.Autofill Destination:=Range("F1:F" & Range("A" & Rows.Count).End(xlUp).Row)

Basically what it is doing is checking to see what the last cell with a value in A is and fill F1 to the F whatever the last cell with value in A. The only way this will work is if you have the same amount of data and rows in column A as you need to fill in F.

I hope that makes sense bc I know what im talking about and it doesnt make sense to me. Just try out the code I have to use this for most of my macros.
110
hey,
hornstar1964,

Thanks for your solution. it worked like a miracle for me...:-)
0
Thank you!!. It is excellent
0
Thank you!
0
Great! Thank you
0
excel987 Posts 3 Registration date Tuesday June 11, 2019 Status Member Last seen June 12, 2019
Jun 11, 2019 at 11:15 PM
Hi hornstar1964,
Is there a way to enter the ranges using a "Named Field" in the macro without having to hardcode them in the macro?
0