Excel VBA: How to Extend a Range Given a Current Selection

Question

I want to do something like:

E18-(1,1) &":" &E18+(1,1)

My intent is to keep the selection of range E18 (value = B) and extend the selection to D16:F20.

If I have a cell's range of E18 and I want to extend the range to D16:F20, how can I do this?

Answer 1

Here is the simple code that I use to resize an existing selection.

Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count + 50).Select

This will add 5 to the row count and 50 to the column count. Adapt to suit your needs.

Answer 2

How to select and extend a range from anywhere on a sheet, to anywhere on a sheet.

This is my first post. I know I'm a little bit late to the party, and it's obvious to me that most of the people here are far far more experienced and skilled than I am. So I doubt my solutions include much of their "big picture" nuanced considerations, but I've verified they work for me and I hope they work for all of you too.

Okay, so back to the question. Here is how I do it.

• Example One
  To do this for the exact scenario posed by your question, if you’re starting at E18 and you want to extend the range to D16:F20, use the code below. As long as you have room for the full range, your active cell can actually be anywhere, and that range will follow it.

  Range(ActiveCell.Offset(-2, -1), ActiveCell.Offset(2, 1)).Select

• Example Two
  If you’ve already selected a range, and then you want to expand it further (let’s say and additional 2 rows down and 1 column to the right), then do this:

  Range(Selection, Selection.Offset(2, 1)).Select

• Example Three
  If you want to select a range of all the contiguous cells containing data, starting from the active cell and continuing down until it reaches a blank cell, and then also add the cells from 1 column to the left, then do this:

  Range(ActiveCell, Selection.End(xlDown).Offset(0, -1)).Select

Answer 3

You can use Application.WorksheetFunction.Offset() which is richer than VBA's Offset and does everything required by the question.
I think it does what Siddharth Rout ExpandRange does, without the need of a UDF.