macro with autofill

[muster101]

hello all,

i have recorded multiple macros that make liberal use of autofill within. i reuse the macro weekly. when i edit the macro, the auotfill command within might look something like:

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B4828")

my problem is, the size of the file is different each week, so i have always edited these recorded lines to over fill:

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B6000")

and this will work until the file grows larger than 6000 lines.

i want to know how i can fix this to adapt to any size file (rows) that might occur.


thank you for any help you can offer!

 

[tk19]

You should be able to use some type of shift-ctrl-down language. Try recording a macro and do shift-ctrl-down in it to see what VB language is produced, then try to implement.

 

[Al Chara]

Like tk19 said try using this:

Range("b2").Select
NumRows = Range(Selection, Selection.End(xlDown)).Count

NumRows is the number of used rows in column B starting with cell B2

 

[muster101]

i can see how the code you supply counts the rows. the physical operation i use when recording the macro is:

colA has header and data in rows 2 thru 100
i insert new col to the right
i place my formula in B2
i then double click lower right hand corner in B2
since cells a2:a100 have data, the formula in B2 copies all the way down to b100 then stops.

when i look at the macro, it then has:

Selection.AutoFill Destination:=Range("B2:B100")

so how would i use the count code you supplied to define the range in the autofill code so that next week when there are 200 rows of data it will adapt?

thanks for the helps gents, i do appreciate!

 

[Al Chara]

The following should work for you, if I understand what you want to do:

Range("a2").Select
fillrange = Range(Selection, Selection.End(xlDown)).Offset(0, 1).Address
Set SourceRange = Worksheets("Sheet1").Range("b2")
SourceRange.AutoFill Destination:=Range(fillrange)

The code finds the used range in column A and then takes the same range in column B and sets it to the fill range. Then it sets the soure range to the formula in Cell B2. Make sure there is something in B2 or it won't work.

Hope this helps.
Kind regards, Al.

 

[Anonymuus]

On 2002-03-15 14:40, muster101 wrote:
i can see how the code you supply counts the rows. the physical operation i use when recording the macro is:

colA has header and data in rows 2 thru 100
i insert new col to the right
i place my formula in B2
i then double click lower right hand corner in B2
since cells a2:a100 have data, the formula in B2 copies all the way down to b100 then stops.

when i look at the macro, it then has:

Selection.AutoFill Destination:=Range("B2:B100")

so how would i use the count code you supplied to define the range in the autofill code so that next week when there are 200 rows of data it will adapt?

thanks for the helps gents, i do appreciate!

You can do all of what you want with the following 2 lines of code. The formula in the code is just for illustration.

Columns(2).Insert
Range([A2], [A2].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=RC[-1]"

To get the syntax for your actual formula, select B2:B3 (or any other range of cells in a single column), switch on the macro recorder, type in your formula, press Ctrl+Enter.
This message was edited by Anonymuus on 2002-03-15 16:06

 

[muster101]

Just wanted to say THANKS! to all responders.

Now...if i could just have someone help me with my NCAA office pool...