Excel Macro - Set and autofill a range? [Solved/Closed]

KT - Mar 19, 2009 at 05:56 PM - Latest reply:  starhorn6419
- Jan 17, 2018 at 01:41 AM
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
See more 

26 replies

hornstar1964 - May 25, 2010 at 11:52 AM
+108
Helpful
3
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.
hey,
hornstar1964,

Thanks for your solution. it worked like a miracle for me...:-)
Thank you!!. It is excellent
+16
Helpful
Hello,
I would like to Autofill the formula to the varying number of columns for example:

Range("A2").AutoFill Destination:=Range("A2:Z2")

Instead of defined "Z"as end column, I would like to keep it varying based on no. of columns I get.

Please help.
WutUp WutUp - Mar 19, 2009 at 08:08 PM
+13
Helpful
9
You want to fill a series of cells based upon the source range.

xlFillSeries instead of xlFillDefault

Try this instead:

Range("F1").AutoFill Destination:=Range("F1:F226"), Type:=xlFillSeries
WutUp WutUp > jimi - Mar 30, 2009 at 09:57 PM
If you are working in the same sheet (not between sheets), then you need to use a "Step" value.
Instead, of selecting one cell, select the first two.

So, if cell F1 contained 0.0, and cell F2 contained 0.5, select F1:F2 to create the step value.

Range("F1:F2").AutoFill Destination:=Range("F1:F226"), Type:=xlFillSeries
jimi > WutUp WutUp - Mar 30, 2009 at 10:44 PM
Hi Wut,
I tried your method but have runtime error 1004
Autofill method of range class failed

here is my piece of code:
Range("G3").Select
ActiveCell.Value = "0"
Range("G3").Value = "0"
Range("G4").Value = "0.5"
Range("G3:G4").AutoFill Destination:=Range("G5:G18"), Type:=xlFillSeries


Please help...thanks
WutUp WutUp > jimi - Mar 31, 2009 at 05:25 AM
Your destination range has to include the starting cell.


Range("G3").Value = "0"
Range("G4").Value = "0.5"
Range("G3:G4").AutoFill Destination:=Range("G3:G18"), Type:=xlFillSeries
robin_suv 2 Posts Tuesday May 19, 2009Registration date May 30, 2009 Last seen - May 30, 2009 at 12:11 AM
hi, I am looking for a macro which will add a column to my sheet and fill a code which is related with my excel file name, I mean to say that suppose there is a file name test which is save in my desktop and I want to open it and run an macro which will add a column to test one or it save another file as test1( both will work) and in that test1 it will add a column say range ("f1:f & bottom) and test which is file name is given a code 23 and in f column 23 must come ???? hope u understand this.... is this possible to do?????

thanks,
robin
ptp24 2 Posts Sunday April 18, 2010Registration date April 18, 2010 Last seen - Apr 18, 2010 at 08:33 PM
WutUp

How can I perform this with keystrokes wen recording a macro? It's the same situation as above I believe. I autofill a sum formula. Say I record the macro when I have 200 rows, 199 autofill. Then if I have 300 the next time, the macro only runs it for the first 200. Thanks.
lethichore 6 Posts Tuesday June 1, 2010Registration date June 1, 2010 Last seen - Jun 1, 2010 at 05:01 AM
+7
Helpful
4
@ hornstar

In ur code u have

SELECTION.Autofill....

i suppose selection is range("F1")?

does anyone know how to do this for a larger range, for example I need to autofill
GG14:IF14 all the way till the last cell in column A
how should I adapt this code above then?

grtz
rizvisa1 4482 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 1, 2010 at 05:54 AM
Have you tried to use the macro recorder feature of excel. If you start the recorder, do it manually and then stop the macro, you would end up with a macro that will do what you want
lethichore 6 Posts Tuesday June 1, 2010Registration date June 1, 2010 Last seen - Jun 1, 2010 at 05:57 AM
yes I have, it gives me :

Range("GG14:IF14").Select
Selection.AutoFill Destination:=Range("GG14:IF3184"), Type:=xlFillDefault

but the last row isn't always 3184, and I can't get it to work with rowcount.

any ideas?
rizvisa1 4482 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 1, 2010 at 06:15 AM
To find the last row on sheet, you can use


lMaxRows = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

But if you only care for last row of column A then use
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row

Selection.AutoFill Destination:=Range("GG14:IF" & lMaxRows ), Type:=xlFillDefault
lethichore 6 Posts Tuesday June 1, 2010Registration date June 1, 2010 Last seen - Jun 1, 2010 at 07:24 AM
thx man works perfectly ;)

i was trying like
Selection.AutoFill Destination:=Range("GG14:IF14" & lMaxRows ), Type:=xlFillDefault

obviously didn't work ^^
+2
Helpful
hey,
hornstar1964,

Thanks for your solution. it worked like a miracle for me.:-)
+1
Helpful
Try This

Selection.AutoFill Destination:=ActiveSheet.Range("F1:F226"), Type:=xlFillDefault
0
Helpful
1
may I have a macro to auto fill in a range based on the value in an adjacent cell while maintain all the other formulas in the row while the additional rows are added with the auto fill range?

please.
rizvisa1 4482 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 20, 2010 at 08:04 AM
June: What you mean by maintaining the all other formula ?
Auto fill would fill in the range you define. If there is some thing in the target range that will be wiped out and replace with the result of autofill
0
Helpful
1
I have set up an excel spreadsheet were I get an average of the lowest score of ten out of twenty, so i'm using 20 rows so when I insert a new row at the top I would like the info in the now 21st row to clear as it's no longer relevent.
rizvisa1 4482 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Sep 19, 2010 at 05:55 AM
Leroy: what formula are you using to do the calculation. There are a lot of things that are not clear, like is there any thing below row 20 or not. etc