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

Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names.
Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as named ranges. - Best answers
Selection.autofill destination:=range - Best answers
Ideogram ai names - Guide
My contacts list names - Guide
Wow monk names - Guide
How to change input names on lg tv ✓ - Smart TV Forum
Laptop keyboard symbol names - Guide

8 responses

Answer 1 / 8

hornstar1964
May 25, 2010 at 11:52 AM

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.

Darda
Aug 13, 2010 at 06:02 AM

hey,
hornstar1964,

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

ignacio M
Feb 20, 2013 at 02:05 PM

Thank you!!. It is excellent

starhorn6419
Jan 17, 2018 at 01:41 AM

Thank you!

Dr
Mar 14, 2019 at 07:40 PM

Great! Thank you

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?

Answer 3 / 8

WutUp WutUp
Mar 19, 2009 at 08:08 PM

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

KT
Mar 20, 2009 at 01:27 PM

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!

WutUp WutUp > KT
Mar 20, 2009 at 09:37 PM

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

KT > WutUp WutUp
Mar 20, 2009 at 11:36 PM

Excellent! That's just what I needed. Thank you so much for your help!

jimi > WutUp WutUp
Mar 30, 2009 at 09:24 PM

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

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

Answer 4 / 8

lethichore Posts 6 Registration date Tuesday June 1, 2010 Status Member Last seen June 1, 2010
Jun 1, 2010 at 05:01 AM

@ 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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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 Posts 6 Registration date Tuesday June 1, 2010 Status Member Last seen June 1, 2010
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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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 Posts 6 Registration date Tuesday June 1, 2010 Status Member Last seen June 1, 2010
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 ^^

Similar discussions

Converting rows of data in notepad to columns

paste data into alternate blank cells

Excel - To generate unique ID

vba copy data from a range and paste to empty column

Excel Macro for deleting rows IF cells not...

Excel if range of cell contain a text put a cell value in

Similar discussions

Subscribe To Our Newsletter!