Excel VBA Row & Region Selection Tips - PTR
Excel VBA Row & Region Selection Tips
Page 1
Excel VBA Row & Region Selection Tips
Learn how to use the following in this document:
Range.End ActiveSheet.UsedRange Selecting The First Row of a Worksheet
ActiveCell.End Method UsedRange.Rows Method
Range.End Property
The Range.End Property returns a range object that represents the last cell in the direction specified.
It accepts one argument which indicates the direction to move in.
Selection.End(xlDown)
Selection.End(xlUp)
Selection.End(xlToLeft) Selection.End(xlToRight)
Equivalent to pressing CTRL + Cursor Down Equivalent to pressing CTRL + Cursor Up Equivalent to CTRL + Cursor Left Equivalent to CTRL + Cursor Right
Not that they will only move to the last filled cell in the specified direction so if there are blank cells, rows or columns in a data region it will not necessarily move to the end of the whole data region.
The following macro was created by recording the following keyboard actions: , , and finally .
PTR associates Limited
ptr.co.uk
Excel VBA Row & Region Selection Tips
Page 2
The UsedRange Property
The UsedRange property can be used to address the range of cells that have data in them. It will take the data range to the furthest row and column containing data, ignoring blank columns and rows that might otherwise cut the range of cells short.
The above macro was applied to the following worksheet:
The result is as follows:
PTR associates Limited
ptr.co.uk
Excel VBA Row & Region Selection Tips
Selecting The First Row of a Worksheet ActiveCell.End Method The following example sets an object variable to the first row of a worksheet.
Page 3
ActiveCell represents the current cell in the worksheet. Range(ActiveCell.End(xlUp).End(xlToLeft), _
ActiveCell.End(xlUp).End(xlToRight))
Defines a range from: ActiveCell.End(xlUp).End(xlToLeft) : Up to the top and to the left (Top Left cell)
To: ActiveCell.End(xlUp).End(xlToRight) : Up to the top and to the Right (Top right cell)
PTR associates Limited
ptr.co.uk
Excel VBA Row & Region Selection Tips For the following worksheet: The macro results in this: For the following worksheet:
The macro results in this:
Page 4
PTR associates Limited
ptr.co.uk
Excel VBA Row & Region Selection Tips For the following worksheet:
The macro results in this:
Page 5
PTR associates Limited
ptr.co.uk
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- a guide to excel and fme safe software
- learn vba for excel
- vba cheat sheets
- basic understanding of macro worksheet range cells excel prince
- porting excel vba to calc starbasic
- excel 2019 basic quick reference
- monte carlo simulation using excel
- sugi 25 using sasr and dde to execute vba macros in excel
- microsoft excel vba free training manual
- 20 useful excel macro examples
Related searches
- excel highlight row if value in column
- excel find row number
- excel find row number match
- vba row and column reference
- excel repeat row 1 on each page
- excel repeat row print
- excel use row number in formula
- excel 2007 row function
- excel variable row number
- excel get row number
- excel insert row every other row
- excel table row number