BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog shows the various techniques within Excel Visual Basic for moving around and selecting cells relative to the active cell or selection. The blog includes a discussion of the Offset, End, Resize, Range, EntireRow, EntireColumn and CurrentRegion methods.
- Selecting Cells with Excel VBA Macros - Relative
- Moving Around a Spreadsheet in VBA - Offset
- Selecting to the End of a Block
- Selecting a Block of Cells using Range
- Resizing Blocks of Cells in Excel VBA macros
- Selecting Entire Columns or Rows in Excel VBA macros (this blog)
Posted by Andy Brown on 16 August 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Selecting Entire Columns or Rows
The EntireColumn and EntireRow properties allow you to turn a block of cells into the columns or rows containing them.
The macros in this blog entry mimic the short-cut keys SHIFT + SPACE BAR (to select rows) and CTRL + SPACE BAR (to select columns).
As an example, suppose that you wanted to use a macro to automatically resize columns B and C below to accommodate the widest text in them:
|We want to go from this ...||... to this|
The command to do this could be as follows:
'resize columns corresponding to current cells
You could use a similar method to resize the rows corresponding to the current selection.
If you can think of any other relative selection or absolute selection techniques I've missed out, let me know. Otherwise, happy coding!