selection reference

[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?

[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.

[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.

[HansV]

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

[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.

[HansV]

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