CODEWITHSUNDEEP
excelrangeexcel-2010vba
oscilatingcretin
oscilatingcretin

Reputation: 10959

What is the VBA code to emulate selecting a block with the CTRL+A shortcut?

In earlier versions of Excel, pressing CTRL+A in a worksheet would literally select all cells. In Excel 2010 (not sure about 2007 or 2003), I've noticed that if you press CTRL+A within a block of cells that contain values, it seems to know to select only the cells in that block. For example, if all cells in range A1:D10 contain values and you hit CTRL+A while the active cell is in that range, it will select only A1:D10. If you press CTRL+A again, only then will it actually select all cells in the worksheet.

So I recorded a macro to see what macro code was being generated when I do this, but it actually writes Range("A1:D10").Select when I hit CTRL+A. This is limiting and not dynamic because now I have to write my own logic to determine the boundaries around the active cell. That's not difficult with methods like ActiveCell.End(xlDown), but I'd like to not have to reinvent a wheel here.

Is there some Excel VBA method like ActiveCell.GetOuterRange.Select? That would be nice.

Upvotes: 17

Views: 62936

Answers (1)

Alex K.
Alex K.

Reputation: 175926

For all dirty cells you can use the Worksheet.UsedRange property:

ActiveSheet.UsedRange.Select

Or for cells surrounding the current cell in a contiguous fashion you can use the Range.CurrentRegion property:

ActiveCell.CurrentRegion.Select

Upvotes: 38

Related Questions