Adjusting Named Ranges Dynamically

[jkwleisemann]

Good morning, everybody!

I have to update a *ton* of tables in an Excel spreadsheet, rolling them forward for the new year. For example, if WRSState is in cells A6:H15, I need to copy rows A7:H15 into A6:H14, clear A15:H15, and then make a couple of other edits that i should be able to figure out.

But I've got about 50 tables, so I'd really rather not do this manually. And they're spread out over a lot of different worksheets.

The sort of thing that should be a macro, I think.

What I'm trying to figure out is how I can set up things like referencing the final row, referencing the final column, that type of thing. I'm pretty sure that I can do things like:

Range("Name").cells(R1C0, R?C?)

But I'm not sure how to fill in the "?" in that.

Also, I'd like to try and iterate through the named ranges, rather than have to type out each one. Can i do something like this?

For i = 1 to Worksheets.Count
For Each Range in Worksheets(i)
(Code and stuff)
Next
Next

Or is there some other way to set this up?

Or should I just start keying and stop trying to be cute about how I do it?

 

[Leandroarb]

Hi, Better late than never!

I use this type:

=OFFSET(Plan2!$A$2,0,0,COUNTA(Plan2!$A:$A)-1)

This formula is inserted in the window opened with CTRL + F3.

In your case, rename the columns.

 

[jkwleisemann]

But will that work when "Plan2" isn't based in cell A1?

 

[mikerickson]

To reference the last row of a named range, you could use the .Rows property, as in

With Range("namedRange")
    MsgBox .Rows(.Rows.Count).Address
End With