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

excel range excel-2010 vba
oscilatingcretin picture oscilatingcretin · Jul 18, 2012 · Viewed 44.5k times · Source

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.

Answer

Alex K. picture Alex K. · Jul 18, 2012

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

Related questions

Creating an Array from a Range in VBA

I'm having a seemingly basic problem but can't find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this by …

Read More
Detect if range is empty

I want to check if a range in Excel is empty. How do I write in VBA code: If Range("A38":"P38") is empty

Read More
VBA paste range

I have simple goal of copying range and pasting it into another spreadsheet. The following code below gives copies, but does not paste. Sub Normalize() Dim Ticker As Range Sheets("Sheet1").Activate Set Ticker = Range(Cells(2, 1), Cells(65, 1)) Ticker.Copy Sheets("…

Read More