CODEWITHSUNDEEP
vbaexcel
Kefkolo
Kefkolo

Reputation: 27

How to use the offset method with named ranges

There maybe better logic to this, but using the note to the user is the only way I understand making this selection work. Maybe you have something better.

I'm pretty much a newbie running Excel 2010 on an Windows XP machine.

I have a named range (say A2:D8) that I call GanttArea. In A8 is a note to the user saying "Any additional lines must be inserted above this line." This is because I don't know how else to make sure the user adds their additional lines in the right place, and I don't know if they will add other lines a few cells later under here that don't apply to what I'm trying to capture.

If I enter 

Sub SelectRange()
    Range("GanttArea").select
End Sub

It will select the named range. I want it to move up one row so not to include my note. If I enter

Sub SelectRange()
    Range("GanttArea").Offset(-1,0).Select
End Sub

It does move the row up for the selection like I want but now it includes the row A1:D1 which are the heading rows. I'm trying to select the data dynamically for a gantt chart so this won't work.

It just occurred to me that maybe the offset is not changing the selection but just moving the same number of selected cells up one row.

How can I make the named range expand or contract so the user can add or delete rows?

Thank you, Kirk

Upvotes: 2

Views: 18444

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You can use the Resize method instead of Offset, so:

Sub SelectRange()
    Dim rng as Range: Set rng = Range("GanttArea")

    Set rng = rng.Resize(rng.Rows.Count - 1, rng.Columns.Count)
    rng.Select

End Sub

Alternatively, you could simply define your named range using the Offset function.

These are a little tricky to set up, but as long as your column A does not contain any blank cells, this should work. In your names manager, enter this formula for the GantArea:

=OFFSET($A$1,1,0,COUNTA($A:$A)-1,4)

If you do this, you should be able to insert additional rows anywhere in the table area and as long as those rows are not empty values in column A, the range will resize dynamically based on that formula.

enter image description here

Upvotes: 6

Related Questions