Excel Range - Revision history

Water: /* Last Cell */

2021-02-25T23:17:31Z

Last Cell

← Older revision		Revision as of 23:17, 25 February 2021
Line 36:		Line 36:
	<syntaxhighlight lang="autoit">Local Const $xlUp = -4162		<syntaxhighlight lang="autoit">Local Const $xlUp = -4162
	With $oWorkbook.ActiveSheet ; process active sheet		With $oWorkbook.ActiveSheet ; process active sheet
	$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; ~~get a Range that contains~~ the last used ~~cells~~		; Returns a Range that contains the last used cells
	$iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; ~~get~~ the row ~~count of~~ the ~~range starting in~~ row~~/column 1~~ and ~~ending at~~ the ~~last~~ used row~~/column~~		$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell)
	$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~~		; 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)		ConsoleWrite("Last used cell in column B: " & $iLastCell & @CRLF)
	EndWith		EndWith

Water: /* Last Cell */

2021-02-25T23:15:08Z

Last Cell

← Older revision		Revision as of 23:15, 25 February 2021
Line 37:		Line 37:
	With $oWorkbook.ActiveSheet ; process active sheet		With $oWorkbook.ActiveSheet ; process active sheet
	$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells		$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		$iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the row count of 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		$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)		ConsoleWrite("Last used cell in column B: " & $iLastCell & @CRLF)

Water at 15:09, 24 July 2019

2019-07-24T15:09:47Z

← Older revision		Revision as of 15:09, 24 July 2019
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.

			== Active cell ==
			The currently selected cell.<br />
			<syntaxhighlight lang="autoit">$oRange = $oExcel.ActiveCell</syntaxhighlight>

	== Current Region ==		== Current Region ==
Line 58:		Line 62:
	== User selection ==		== User selection ==
	Returns the range selected by the user:		Returns the range selected by the user:
	<syntaxhighlight lang="autoit">$oRange = $oExcel.~~Slection~~</syntaxhighlight>		<syntaxhighlight lang="autoit">$oRange = $oExcel.Selection</syntaxhighlight>

	[[Category:UDF]]		[[Category:UDF]]

Water: /* Used Range */

2018-11-07T16:49:08Z

Used Range

← Older revision		Revision as of 16:49, 7 November 2018
Line 41:		Line 41:

	== Used Range ==		== 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 />		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 does not contain row 1 and column 1!		Note that the range in the following example does not contain row 1 and column 1!
	<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.UsedRange</syntaxhighlight>		<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.UsedRange</syntaxhighlight>
	[[File:Range_UsedRange.jpg]]<br />		[[File:Range_UsedRange.jpg]]<br />

Water at 15:49, 28 October 2018

2018-10-28T15:49:49Z

← Older revision		Revision as of 15:49, 28 October 2018
Line 55:		Line 55:
	EndWith</syntaxhighlight>		EndWith</syntaxhighlight>
	[[File:Range_UsedRangeFull.jpg]]<br />		[[File:Range_UsedRangeFull.jpg]]<br />

			== User selection ==
			Returns the range selected by the user:
			<syntaxhighlight lang="autoit">$oRange = $oExcel.Slection</syntaxhighlight>

	[[Category:UDF]]		[[Category:UDF]]

Water: /* Used Range */

2016-07-12T13:59:47Z

Used Range

← Older revision		Revision as of 13:59, 12 July 2016
Line 41:		Line 41:

	== Used Range ==		== Used Range ==
	Returns a ~~Range~~ 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 />		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 />
	Note that the range does not contain row 1 and column 1!		Note that the range does not contain row 1 and column 1!
	<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.UsedRange</syntaxhighlight>		<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.UsedRange</syntaxhighlight>

Water: /* Last Cell */

2016-07-12T13:59:10Z

Last Cell

← Older revision		Revision as of 13:59, 12 July 2016
Line 22:		Line 22:

	== Last Cell ==		== Last Cell ==
	Returns the last used cell (bottom right) in the used range.		Returns a range object including the last used cell (bottom right) in the used range.
	<br />		<br />
	External Link: [http://www.ozgrid.com/VBA/ExcelRanges.htm Excel Ranges: Finding the Last Cell in a Range]<br />		External Link: [http://www.ozgrid.com/VBA/ExcelRanges.htm Excel Ranges: Finding the Last Cell in a Range]<br />
Line 28:		Line 28:
	[[File:Range_LastCell.jpg]]<br />		[[File:Range_LastCell.jpg]]<br />
	<br />		<br />
	Returns the last used cell in column "B".<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.		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		<syntaxhighlight lang="autoit">Local Const $xlUp = -4162

Water: /* Entire Row / Entire Column */

2016-07-12T13:58:01Z

Entire Row / Entire Column

← Older revision		Revision as of 13:58, 12 July 2016
Line 16:		Line 16:

	== Entire Row / Entire Column ==		== Entire Row / Entire Column ==
	~~Selects~~ the entire row(s) of the specified range.<br />		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.		Use method "EntireColumn" to select the columns of the specified range.
	<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.Range("B2").EntireRow</syntaxhighlight>		<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.Range("B2").EntireRow</syntaxhighlight>

Water: /* Empty Cells */

2016-07-12T13:57:05Z

Empty Cells

← Older revision		Revision as of 13:57, 12 July 2016
Line 11:		Line 11:

	== Empty Cells ==		== Empty Cells ==
	~~Marks~~ all empty cells in the specified range.<br />		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>
	[[File:Range_EmptyCells.jpg]]		[[File:Range_EmptyCells.jpg]]

Water at 13:16, 26 April 2016

2016-04-26T13:16:27Z

← Older revision		Revision as of 13:16, 26 April 2016
Line 41:		Line 41:

	== Used Range ==		== Used Range ==
			Returns a Range 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 />
			Note that the range does not contain row 1 and column 1!
	<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.UsedRange</syntaxhighlight>		<syntaxhighlight lang="autoit">$oRange = $oWorkBook.Activesheet.UsedRange</syntaxhighlight>
	~~Returns a Range 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 />~~
	~~Note that the range does not contain row 1 and column 1!<br />~~
	[[File:Range_UsedRange.jpg]]<br />		[[File:Range_UsedRange.jpg]]<br />
	<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>		<syntaxhighlight lang="autoit">$sUpperLeftCell = $oWorkbook.ActiveSheet.UsedRange.Cells(1, 1).Address</syntaxhighlight>
	~~Returns the address of the upper left cell of the used range. In this examle: B2<br />~~
	<br />		<br />
			This lines extend the used range to start with cell A1.<br />
	<syntaxhighlight lang="autoit">With $oWorkbook.ActiveSheet		<syntaxhighlight lang="autoit">With $oWorkbook.ActiveSheet
	$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell)		$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell)
	$oRange = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column))		$oRange = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column))
	EndWith</syntaxhighlight>		EndWith</syntaxhighlight>
	~~This lines extend the used range to start with cell A1.<br />~~
	[[File:Range_UsedRangeFull.jpg]]<br />		[[File:Range_UsedRangeFull.jpg]]<br />

	[[Category:UDF]]		[[Category:UDF]]