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

excel, excel-2010, range, vba

Solution

For all dirty cells you can;

ActiveSheet.UsedRange.Select

Or for cells surrounding the current cell in a contiguous fashion you can;

ActiveCell.CurrentRegion.Select

Problem

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.

Original source