Menu
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.
390
Hello, the Ctrl-A keyboard shortcut will select the whole sheet, unless Excel can determine a table with boundaries that are less than the whole sheet. The equivalent VBA command is to use the CurrentRegion property of the Range object.
In the Macro Name column, press the key or keyboard shortcut to which you want to assign the action or set of actions. In the Action column, add the action that you want the key or keyboard shortcut to perform. For example, you can add a RunMacro action that runs the Print Current Record macro when CTRL+P is pressed.
In VBA we can select any range of cells or a group of cells and perform different set of operations on them, selection is a range object so we use range method to select the cells as it identifies the cells and the code to select the cells is “Select” command, the syntax to use for selection is range(A1:B2). select.
Selecting a Single Cell Using VBARange(“A1”) tells VBA the address of the cell that we want to refer to. Select is a method of the Range object and selects the cells/range specified in the Range object. The cell references need to be enclosed in double quotes.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
