Autofill up to last cell of Range

[jeff.p]

Hello,

I've created a named Range called "AutoFill" within column H20:H40

How can I apply an auto count (autofill?) sequence (1,2,3,4....20) from the first cell begining with 1, up to the last cell of this range, using a VBA script?

** Rows will be continually added & deleted within my worksheet, so the position & length of this range will vary.

Thanks for any help


Jeff

[Ron Coderre]

First, it's a BAD idea to name ranges (or pretty much anything else) with an Excel object, method, or property name. It makes the code more difficult to read and can have unintended consequences.

Examples:
A sheet named "sheet"
a range named "select"...or "AutoFill"

In your situation, I'd name the range rngAutoFill

See if this works for you

Please Login or Register  to view this content.

Is that something you can work with?

[Bartek]

Hi,

I've created a named Range called "AutoFill" within column H20:H40
How can I apply an auto count (autofill?) sequence (1,2,3,4....20) from the first cell begining with 1, up to the last cell of this range, using a VBA script?

Try something like that:

Please Login or Register  to view this content.

[jeff.p]

Thanks, Ron, Bartek!

Both codes worked wonderfully.

...

If I could possibly ask one more question?

As a follow up to my initial quarry... How could I use he same scenario.. ie: A range called AutoFill H20:H40, but in this case, rather than fill with a sequential number, to autofill with a single word. "Act01"

eg:

Act01
Act01
Act01
Act01
Act01
Act01

etc..


Thanks again for the help

Jeff

[Ron Coderre]

Try this line of code:

Please Login or Register  to view this content.

Does that help?

[jeff.p]

Thanks, Ron

Tried pasting this into a module, but received this error:

Run-time error 424
object required

any ideas?

[Ron Coderre]

Yeah.....sloppiness on my part (sorry)

Range("AutoFill").Value = "Act01"
or
Range("H20:H40").Value = "Act01"

Does that help?

[jeff.p]

Thanks, Ron!


...it helps indeed!