Expand range based on count

[beeawwb]

Hi all,

I've got a couple problems working with ranges in VBA. I have a set of values in a table, and I want to clear a row each time I run my macro. This was fine when I had a set range (Sheets("Calculator").Range("F23:F46").Value = "") but now I have some code which inserts and deletes rows from my table.

So now, my range could be F23:F30, and then with a new row added it goes from F23:F31. Or, on the flip side it could decrease to F23:F29.

I have code which tells me how many values are in the list, so I know where the first row always is (F23) and I know where the last row is (Row 23 + TotalCount-1) But I don't know how to turn that into a lettered range. I can't exactly say "F23:F23+TotalCount-1"

So that's problem 1 with ranges.

Problem 2 comes from adding a new row. My table has automatically calculated cells in Column E, G and I. I usually use autofill to copy the cells down. Works fine when doing it manually. As I understand it, to do this automatically, I'd need to reference a Range.

Eg, Selection.AutoFill Destination:=Range(E29:E30). Same problem as above, I can't select the last row, expand that by 1, and use the Range for Auto fill.

Anybody know what I can do to acheive my goals?

Thanks in advance,

-Bob

[duane]

For the first one

Sheets("Calculator").activate
Range(cells(23,6),cells(23+TotalCount-1,6)).Value = "")

should work

for the second one, why not something like

Range(Cells(A, B), Cells(C, D)).Name = "myrange"
'prior to 
Selection.AutoFill Destination:=Range("myrange")

you of course need to define A, B, C, D

[beeawwb]

Hi duana, thanks for your speedy reply!

Your solution to part 1 works great, no issues there. I feel silly for not realising I can define a range based on 2 Cells(x,y) expressions.

As for part 2 though, I'm getting an error.

Range(Cells(TotalCount + 22, 5), Cells(TotalCount + 23, 5)).Name = "myrange"
'prior to
Selection.AutoFill Destination:=Range("myrange")

I can see in debug mode when hovering over .Name, that the address is correctly referenced. But when hovering over myrange, it shows "", which makes the AutoFill fail the range criteria.

I actually worked out a solution a couple minutes ago which allows me to do what I want... but it is based on selecting a cell, expanding a range, choosing the window selection, and it's very clunky. I'd like to explore out your solution which seems much more robust.

Thanks again,

-Bob

[beeawwb]

Sorry, that was me being an idiot again. Didn't get much sleep last night. :P

I forgot that you need to have the cell TO autofill selected first (

Cells(TotalCount+22,5).Select

) before you can use Selection.Autofill for a range. It's all good now, but I did change the order / selection types. My final code...

myrange = Range(Cells(TotalCount + 22, 5), Cells(TotalCount + 23, 5)).Address
Cells(TotalCount + 22, 5).Select
Selection.AutoFill Destination:=Range(myrange)

Thanks again,

-Bob