Search : in
By :

Excel Macro - Set and autofill a range?

Last answer on May 30, 2009 5:11:09 am BST KT, on Mar 19, 2009 9:56:29 pm GMT 
 Report this message to moderators

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

Configuration: Windows XP
Internet Explorer 7.0

Best answers for « Excel Macro Set and autofill a range? » in :
Excel Macro for deleting rows IF cells not... Show
Excel Macro to create a chart Show
Excel – Macro to detect and hide blank rows Show Excel – Macro to detect and hide blank rows Issue Solution Note Issue I want a macro to unhide about 20 blank rows copy values into the top row then hide the remaining rows (some cells have fill though) then the next time it will...
Excel – Macro for copy & paste selected range ShowExcel – Macro for copy & paste selected range Issue Solution Note Issue I have a summary of data by month in one sheet and the raw data in another sheet. Instead of creating multiple worksheets for my raw data for each month, I want...
How to convert Excel into PDF? ShowHow to convert Excel into PDF? Here is a small tips about how to convert your excel files into PDF for your presentation. Step 1 PDF995 is software that gets installed on your computer which enables you to print any sources of document to...
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL + ; in the chosen cell. To insert current time, press CTRL+...
Download Excel Macro Processor Show
Download Super Macro ShowSuper macro is a free software which allows to create macro under Windows in order to activate diverse automatic actions. Apart being free, this software is easy to use and requires no knowledge in programming. You just simply click buttons, then...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

WutUp WutUp, on Mar 20, 2009 12:08:19 am GMT
  • +3

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

   
Reply to WutUp WutUp

2

KT, on Mar 20, 2009 5:27:19 pm GMT

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!

   
Reply to KT

3

WutUp WutUp, on Mar 21, 2009 1:37:43 am GMT
  • +5

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

   
Reply to WutUp WutUp

4

KT, on Mar 21, 2009 3:36:34 am GMT

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

   
Reply to KT

5

jimi, on Mar 31, 2009 2:24:22 am BST
  • +1

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

   
Reply to jimi

6

WutUp WutUp, on Mar 31, 2009 2:57:49 am BST

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

   
Reply to WutUp WutUp

7

jimi, on Mar 31, 2009 3:44:04 am BST

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

   
Reply to jimi

8

WutUp WutUp, on Mar 31, 2009 10:25:16 am BST

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

   
Reply to WutUp WutUp

9

 robin_suv, on May 30, 2009 5:11:09 am BST

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

   
Reply to robin_suv
Ask your question Reply
They need your help