"floating" named range...is it possible to name a range or make a macro to select more than one cell anywhere?

[sadams1]

hello & thank you for taking time to see if this insane question is even worth asking...

I'd like to select 3 contiguous cells in a row (i.e A1:A3).
Could use named ranges if it was static but rather would like to land in a cell that isn't A1, like C1 & be able to run a macro or name a "floating" range that would select C1:C3.

Like usual, I hate having to ask for help but I've gone through stuff here & elsewhere to no avail so finally crying uncle.

 

[Micron]

Not sure what any of that means. Maybe what you're asking about is a dynamic named range? Or maybe by "name a floating range" you want a range input box:
Set myRangeVariableHere = Application.InputBox(" ", " ", Type:=8)

 

[Skyybot]

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.CountLarge = 1 Then Range(Selection, Selection.Offset(0, 2)).Select
End Sub

 

[sadams1]

Appreciate the reply! Been messing with the code & I have zero formal training & just a hack at best so when I copy & paste the code & run it, it looks like all the cells in the sheet are being selected. Working on screenshots but I'll be damned if I can't figure out how to get a stupid jpg small enough to be uploaded.

 

[MARK858]

copy & paste the code & run it

I haven't tested the code but not sure what you mean by Run it? it is worksheet event code and so goes in the Worksheet module (right click your sheet tab and click View Code), then the code runs every time you change your selection of a single cell (not sure why the code is using selection rather than Target either, not that it makes any real difference for this task)

 

[sadams1]

sorry for the confusion! I'm looking to define a range of two or more contiguous cells (i.e. A1:A3) & then be able to hit a keyboard shortcut (i.e. Ctrl + A) & that same range "shape" of the cells will be selected. It would look like this:

1. define A1:A3 as a range or shape
2. click on cell C1
3. run macro or go to "named range" & the result would be that C1:C3 is selected.

another example
2. click of cell Z3
3. run macro or go to "named range" & the result would be that Z3:Z6 is selected.


Why do I want to do this? I have a spreadsheet with many columns & already have a useful macro that resizes & realigns all the notes. I could just select the entire column & run the macro but here's the problem, the first row has a comment that is is a different size so the macro won't work. Having a way to select a "dynamic" range would enable me to just land on any cell, run the macro & that range or shape of cells would be selected.

Long story short, I'd like to see if code would be able to just select a defined range of cells anywhere.

 

[MARK858]

Not something I'd ever do but...

Sub selectRng()
Dim myRng As Range

Set myRng = Range("A1:A3")

With myRng
  ActiveCell.Resize(.Rows.Count, .Columns.Count).Select
End With

End Sub

or with a Named Ranged called NamedRng

Sub selectRng2()

With Range("NamedRng")
  ActiveCell.Resize(.Rows.Count, .Columns.Count).Select
End With

End Sub

 

[sadams1]

THAT'S IT! been messing with this & having my group do the same...AMAZING. Thank you very much as it is going to help us immensely!

 

[MARK858]

You're welcome, happy we could help