On this page you will find some special ranges and how to define them in AutoIt.
As input file I will use the following Excel workbook. All cells which have a value now or had a value before (means: all cells which had been touched by the user or a script) are displayed in grey.
All examples show cells of the selected range in yellow.
The current region is a range bounded by any combination of blank rows and blank columns.
Note that cell B6 is not part of the region. The cell has been touched before but now is blank and hence bounds the region.
$oRange = $oWorkBook.Activesheet.Range("B4").CurrentRegion
Returns a range object including all empty cells in the specified range.
$oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeBlanks)
Entire Row / Entire Column
Returns a range object including the entire row(s) of the specified range.
Use method "EntireColumn" to select the columns of the specified range.
$oRange = $oWorkBook.Activesheet.Range("B2").EntireRow
Returns a range object including the last used cell (bottom right) in the used range.
External Link: Excel Ranges: Finding the Last Cell in a Range
$oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)
Local Const $xlUp = -4162 With $oWorkbook.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number ConsoleWrite("Last used cell in column B: " & $iLastCell & @CRLF) EndWith
Returns a range object including all cells which have a value now or had a value before (means: all cells which had been touched by the user or a script).
Note that the range in the following example does not contain row 1 and column 1!
$oRange = $oWorkBook.Activesheet.UsedRange
$sUpperLeftCell = $oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address
This lines extend the used range to start with cell A1.
With $oWorkbook.ActiveSheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $oRange = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)) EndWith
Returns the range selected by the user:
$oRange = $oExcel.Slection