selection reference

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

selection reference

Post by dasadler »

How would I refer (in VBA) to a range I have selected on an Excel worksheet? For example, if I select A1:F6 (although I may select other ranges at other times), how would I refer to this selected area within code so I can do something with it? Must I define the section as a name first?
Don

User avatar
HansV
Administrator
Posts: 77588
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: selection reference

Post by HansV »

The keyword Selection refers to the currently selected cells, whatever they are.

Examples:

Selection.Font.Bold = True

Selection.ClearContents

Note: if you select an object on the worksheet, such as a shape or chart, Selection will refer to that object. ActiveWindow.RangeSelection will refer to the selected cells even if the current selection is an object.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: selection reference

Post by dasadler »

Thank you - I wanted to do a paste value and came up with this:

Sub convert2values()
Selection = Selection.Value
End sub

simple but seems to work.
Don

User avatar
HansV
Administrator
Posts: 77588
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: selection reference

Post by HansV »

Yep, that's the easiest macro solution to replace formulas with values. It also works to convert numbers stored as text to numbers.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: selection reference

Post by dasadler »

That's good to know. I am trying to populate a personal.xlsb with useful macros and I recall you said that you have one that replaces formulas with values - thought it would be good to have. I found, however, that the undo feature has no effect if that macro is invoked.
Don

User avatar
HansV
Administrator
Posts: 77588
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: selection reference

Post by HansV »

Unfortunately, running VBA code in Excel disables undo, even in Excel 2007.
Best wishes,
Hans