VBA to reference currently selected multi-cell range?

0
votes

I can post some code, but my question is so simple I don’t think that’s needed. I think I’m just struggling to know what to google.

I want to select (with my mouse) a range of multiple contiguous cells all in the same row and then execute a subroutine that will have a way to reference all the cells in that selected range. Is there a built in method for that? Something like:

Worksheet.ActiveRange(1,1).Value2

would reference the first cell and then

Worksheet.ActiveRange(1,2).Value2

would reference the second cell.

Or 

MyFunction(Worksheet.ActiveRange)
1
excelvba
So you're trying to use Selection.Areas? Or just Selection? – BigBen
What do you mean by the "built in method"? – Gene
@Gene I mean “method” as in function or subroutine. – Neal Davis

1 Answers

3
votes

If all the cells are contiguous, use Selection.

If the cells are not all contiguous, loop through Selection.Areas.

Since Selection may not necessarily be a Range, you can test for that first:

If TypeOf Selection Is Range Then...

You can also use Application.InputBox to get the range from user selection.