Excel Range: Difference between revisions

From AutoIt Wiki
Jump to navigation Jump to search
No edit summary
 
(27 intermediate revisions by 2 users not shown)
Line 3: Line 3:
[[File:Range.jpg]]<br />
[[File:Range.jpg]]<br />
All examples show cells of the selected range in yellow.
All examples show cells of the selected range in yellow.
== Used Range ==
 
<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.UsedRange</syntaxhighlight>
== Active cell ==
Selects 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).<br />
The currently selected cell.<br />
Note that the range does not contain row 1 and column 1!<br />
<syntaxhighlight lang="autoit">$oRange = $oExcel.ActiveCell</syntaxhighlight>
[[File:Range_UsedRange.jpg]]<br />
 
== EntireRow / EntireColumn ==
<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.Range("B2").EntireRow</syntaxhighlight>
Selects the entire row(s) of the specified range.<br />
[[File:Range_EntireRow.jpg]]<br />
Use method "EntireColumn" to select the columns for the specified range.
== Current Region ==
== Current Region ==
<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.Range("B4").CurrentRegion</syntaxhighlight>
The current region is a range bounded by any combination of blank rows and blank columns.<br />
The current region is a range bounded by any combination of blank rows and blank columns.<br />
[[File:Range_CurrentRegion.jpg]]<br />
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.
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.
== Empty cells ==
<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.Range("B4").CurrentRegion</syntaxhighlight>
[[File:Range_CurrentRegion.jpg]]
 
== Empty Cells ==
Returns a range object including all empty cells in the specified range.<br />
<syntaxhighlight lang="autoit">$oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeBlanks)</syntaxhighlight>
<syntaxhighlight lang="autoit">$oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeBlanks)</syntaxhighlight>
Marks all empty cells in the specified range.<br />
[[File:Range_EmptyCells.jpg]]
[[File:Range_EmptyCells.jpg]]<br />
 
== Entire Row / Entire Column ==
Returns a range object including the entire row(s) of the specified range.<br />
Use method "EntireColumn" to select the columns of the specified range.
<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.Range("B2").EntireRow</syntaxhighlight>
[[File:Range_EntireRow.jpg]]
 
== Last Cell ==
Returns a range object including the last used cell (bottom right) in the used range.
<br />
External Link: [http://www.ozgrid.com/VBA/ExcelRanges.htm Excel Ranges: Finding the Last Cell in a Range]<br />
<syntaxhighlight lang="autoit">$oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)</syntaxhighlight>
[[File:Range_LastCell.jpg]]<br />
<br />
Returns a range object including the last used cell in column "B".<br />
NB: If the script moved up to row 1 you need to check if the cell is really the last used cell or just the top of the range.
<syntaxhighlight lang="autoit">Local Const $xlUp = -4162
With $oWorkbook.ActiveSheet ; process active sheet
; Returns a Range that contains the last used cells
$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell)
; Get the row count of the range starting in row/column 1 and ending at the last used row/column
$iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count
; 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
$iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row
ConsoleWrite("Last used cell in column B: " & $iLastCell & @CRLF)
EndWith
</syntaxhighlight>
[[File:Range_LastCellInColumn.jpg]]
 
== Used Range ==
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).<br /><br />
Note that the range in the following example does not contain row 1 and column 1!
<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.UsedRange</syntaxhighlight>
[[File:Range_UsedRange.jpg]]<br />
<br />
Returns the address of the upper left cell of the used range. In this examle: B2
<syntaxhighlight lang="autoit">$sUpperLeftCell = $oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address</syntaxhighlight>
<br />
This lines extend the used range to start with cell A1.<br />
<syntaxhighlight lang="autoit">With $oWorkbook.ActiveSheet
$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell)
$oRange = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column))
EndWith</syntaxhighlight>
[[File:Range_UsedRangeFull.jpg]]<br />
 
== User selection ==
Returns the range selected by the user:
<syntaxhighlight lang="autoit">$oRange = $oExcel.Selection</syntaxhighlight>
 
[[Category:UDF]]

Latest revision as of 23:17, 25 February 2021

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.

Active cell

The currently selected cell.

$oRange = $oExcel.ActiveCell

Current Region

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

Empty Cells

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

Last Cell

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)



Returns a range object including the last used cell in column "B".
NB: If the script moved up to row 1 you need to check if the cell is really the last used cell or just the top of the range.

Local Const $xlUp = -4162
With $oWorkbook.ActiveSheet ; process active sheet
	; Returns a Range that contains the last used cells
	$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell)
	; Get the row count of the range starting in row/column 1 and ending at the last used row/column
	$iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count 
	; 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
	$iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row 
	ConsoleWrite("Last used cell in column B: " & $iLastCell & @CRLF)
EndWith

Used Range

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



Returns the address of the upper left cell of the used range. In this examle: B2

$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


User selection

Returns the range selected by the user:

$oRange = $oExcel.Selection