Excel Macro - Set and autofill a range?

Solved/Closed
Report
-
Posts
3
Registration date
Tuesday June 11, 2019
Status
Member
Last seen
June 12, 2019
-
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
Posts
3
Registration date
Tuesday June 11, 2019
Status
Member
Last seen
June 12, 2019

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
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.
16
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
13
Thanks for your help. Do you know how I can make the Range be dynamic? (Instead of F1:F226 I want it to be F1:the last populated row in column F). (So instead of F226, which happens to be the last populated cell in column F for this file, I want it to always find the last populated cell in column F for that particular file.)

Thanks again!
0
> KT
Dim BtmRow As Long
Bottom = Sheets("Put the name of your sheet here").Range("F65536").End(xlUp).Row

Range("F1").Select
Selection.AutoFill Destination:=Range("F1:F" & Bottom), Type:=xlFillSeries
0
> WutUp WutUp
Excellent! That's just what I needed. Thank you so much for your help!
0
> WutUp WutUp
Hi,

Dim BtmRow As Long
Bottom = Sheets("Put the name of your sheet here").Range("F65536").End(xlUp).Row

Range("F1").Select
Selection.AutoFill Destination:=Range("F1:F" & Bottom), Type:=xlFillSeries

Instead of using XlFillSeries, what else can we use to have excel fill in at half increment?
eg: 0, 0.5, 1, 1.5, 2, 2.5, etc?

thanks heapss for helping
0
> jimi
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
0
Posts
6
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
June 1, 2010

@ 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
7
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
0
Posts
6
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
June 1, 2010

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?
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
0
Posts
6
Registration date
Tuesday June 1, 2010
Status
Member
Last seen
June 1, 2010

thx man works perfectly ;)

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

obviously didn't work ^^
0
hey,
hornstar1964,

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

Selection.AutoFill Destination:=ActiveSheet.Range("F1:F226"), Type:=xlFillDefault
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.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
0