Setting boundaries to Usedrange.Rows?

[centagon]

I'm trying to create a basic data entry script, where I pull sets of excel values into an array and then later copy them into another program. Currently I am using _RangeRead with specific dimensions (C17:H36) which works fine, except I need it to only copy values from used cells in this range. I realise I could use Usedrange.Rows, but I was not able to format in such a way that it works only within the given dimensions and disregards the remainder of the sheet.

Alternatively, if I could somehow easily parse the given dimensions for the last used row, then that could work for me too, as I would be able to limit the loop function for pasting the data accordingly.

 

Thanks!

[FrancescoDiMuro]

@centagon

From the Help file about _Excel_RangeRead() function:

; Read the formulas of a cell range (all used cells in column A)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

Did you try it?

Edited August 28, 2018 by FrancescoDiMuro

[water]

The wiki lists a lot of ways to work with ranges.

[centagon]

18 hours ago, FrancescoDiMuro said:

@centagon

From the Help file about _Excel_RangeRead() function:

; Read the formulas of a cell range (all used cells in column A)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

Did you try it?

I did. I want the range to be between cells C17 and H36, and this one captures all cells within those columns. Can I nest a usedrange.rows inside of it to further reduce the range? Or is there a better way

[water]

How about:

; Read the formulas of a cell range (all used cells in column A)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Range("C17:H36"), 2)

Unused cells in the range are empty. Then simply ignore empty cells.

Edited August 29, 2018 by water

[centagon]

The array still captures the empty values and increases the size of the array... But yeah, you're right I was running a loop to enter all of the values in the array, and simply set a check to stop for empty values. I was planning to first find the upper bounds of the array, then limit the loop according to that.

 

Thanks!

[water]

Local $iRows = $oWorkbook.ActiveSheet.Usedrange.Rows.Count

returns the number of rows of the used range. Note that if the first used cell is in row 3 and the last in row 7 you will get a result of 5.