Expand range based on count

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

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.

Please Login or Register  to view this content.

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

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 (

Please Login or Register  to view this content.

) 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...

Please Login or Register  to view this content.

Thanks again,

-Bob