Getting Value from Last Used Row in a defined range

[Siwa]

Hi autoit community. I'm currently working on a project and I need to read the last entered value in a row defined by a variable. ( for example from I2:AM2 )

I have looked at this wiki link, but I could not find the suitable code, as it directs me to a page with Excel.VBA codes which I do not understand very well.

If anyone can direct me to the correct path I'll be thankful.

 

 

 

[water]

The code box below the link shows the Autoit code to access the last cell of the range.

[Siwa]

Thanks, but the code gives the the last row number of the column , not the last column in the row, and how can I restrict the search range? 

Edited October 21, 2020 by Siwa

[water]

Try something like this (untested):

$oRange = $oWorkbook.ActiveSheet.Range("I2:AM2").SpecialCells($xlCellTypeLastCell)

 

[Siwa]

It gives me no result, and no error.

 

$oRange  = $oWorkbook.Worksheets(3).Range("I2:AM2").SpecialCells($xlCellTypeLastCell)
    ConsoleWrite("Last used cell in the row : " & $oRange & @CRLF)

The result is :

Quote

Last used cell in the row : 

 

[water]

The code I supplied returns a range object. ConsoleWrite only displays strings. That's why you do net get a visible result.
Try:

$oRange = $oWorkbook.ActiveSheet.Range("I2:AM2").SpecialCells($xlCellTypeLastCell)
Consolewrite($oRange.Address & @CRLF)

 

Edited October 21, 2020 by water

[Nine]

maybe this is what you want :

#include <Constants.au3>
#include <Math.au3>
#include <Excel.au3>

$oExcel = _Excel_Open(True)
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error creating Excel object! @error = " & @error & ", @extended = " & @extended)

$oWorkBook = _Excel_BookAttach("s2017.xls", "filename")
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error attaching to the workbook! @error = " & @error & ", @extended = " & @extended)

; get actual last col/row used
$col = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).column
$row = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).row
ConsoleWrite ($col & "/" & $row & @CRLF)

; get specific range col/row
With $oWorkbook.ActiveSheet.Range("A1:Z200")
  $lcol = .Columns(.Columns.Count).Column
  $lrow = .Rows(.Rows.Count).Row
EndWith
ConsoleWrite ($lcol & "/" & $lrow & @CRLF)

; show the last used col/row used in a specific range
ConsoleWrite (_Min($col,$lcol) & "/" & _Min($row,$lrow) & @CRLF)

 

[water]

This works for me:

#include <Excel.au3>

Global Const $xlToRight = -4161 ; To right

$oExcel = _Excel_Open(True)
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error creating Excel object! @error = " & @error & ", @extended = " & @extended)

$oWorkBook = _Excel_BookOpen($oExcel, @Scriptdir & "\test_lastCell.xlsx")
If @error Then Exit Msgbox($MB_ICONERROR, "Error", "Error opening the workbook! @error = " & @error & ", @extended = " & @extended)

$oLastCol = $oWorkbook.ActiveSheet.Range("A2").End($xlToRight)
ConsoleWrite($oLastCol.Address & @CRLF)

Details can be found here: https://analystcave.com/excel-vba-last-row-last-column-last-cell/

Edited October 21, 2020 by water

[Siwa]

thanks for your help. both were great but my problem still is not solved.

Because if there is an empty cell in the row, your codes will stop before that empty cell and it will ignore reading the row in the given range.

 

BTW, My Excel file is filled with tons of data, and I want to read them all once, and then read the array. I think his way, it will be much faster. Should I do that instead ?

Edited October 21, 2020 by Siwa

[water]

By "tons of data" how many rows and columns are you talking about?

[Siwa]

Around 300 rows and 31 columns.

If I use a for...Next statement for each of the 300 rows, it takes about 20 seconds. But I guess reading the whole sheet into an array, it will take maybe less than 5 seconds.

And you have to add these timings o my other functions which also reads from other sheets of the excel.

I want your technical opinion on this.

[Nine]

Ok. Give us the xls in question, and tell us what you want to get out of it.  So we can stop GUESSING what you are after...

[water]

300 x 31 isn't too big. Reading all cells into an array with _Excel_RangeRead is much faster thean reading every cell.
Looping through the array will then just access the array in memory.

[seadoggie01]

There are tons of examples of how to do this. It really depends on your data, but I've found this to be the best method. I usually use something like:

Func LastUsedColumnInRow($oSheet, $iRow) ; Requires <Excel.au3>

    If Not ObjName($oSheet) = "_Worksheet" Then Return SetError(1, 1, False) ; Not a worksheet object
    If Not IsInt($iRow) Then Return SetError(1, 2, False) ; Bad parameter
    Local $oErr = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oErr
    ; Get the number of cells in a single row (aka the number of columns - works with xls and xlsx)
    Local $iColumnCount = $oSheet.Cells($iRow, 1).EntireRow.Cells.Count
    If @error Then Return SetError(2, 0, False) ; Invalid row
    ; Not defined in Excel.au3 and I don't like magic numbers
    Local $xlToLeft = -4159
    ; Get the last cell in the row and move left
    Local $iColumn = $oSheet.Cells($iRow, $iColumnCount).End($xlToLeft).Column
    If @error Then Return SetError(3, 0, False) ; I don't think this will ever happen

    Return $iColumn

EndFunc

[GokAy]

@seadoggie01 Siwa is saying he has empty cells too, so would "row.cell.count" work in that case? Also some empty rows in between

In Excel (not sure about AutoIt), you can just use Sheets("name").UsedRange for getting whole range with spaces in between.

Then you can do something like this

Range = Sheets("sheetname").UsedRange
MaxColInRange = Range.Column + Range.Columns.Count -1

For i = Range.Row to (Range.Row + Range.Rows.Count-1)
    MaxColumnInRow_i = Cells(i, MAxColInRange + 1).End(xlToLeft).Column ; +1 so that you can catch the row with max columns
    Array[i] = MaxColumnInRow_i
next i


-----------------
Adding Range.Row and Range.Column to row/column counts since data may not be starting from row = 1, column = 1

Note: From the page linked a few posts above (The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time)

Or if that doesn't work:

A bit more lengthy and can't really test this at the moment, but will give the algorithm. See how you guys like it.

MaxEmptyRows= 10    ; Max. Expected Empty Rows InBetween Data Ranges
MaxExpectedColumns = 100  ; any suitable number that is sure to be greater than any max column in data
MaxColumnNo = 1
EmptyRowCount=0     ; Increment by 1 if row.cells.count = 0
i = FirstRow    ; Row where the data starts

Do While EmptyRowCount <= MaxEmptyRows
    If Rows(i).Cells.Count > 0 Then
        EmptyRowsCount = 0  ; Reset counter
        CheckCol = 1    ; Set Col to 1 for new row
        Do While CheckCol < MaxExpectedColumn
            CheckCol = Cells(i, CheckCol).End(xlToRight).Column
            If CheckCol > MaxExpectedColumn Then    ; extreme rightmost column
                ; Do Nothing
            Else
                If CheckCol > MaxColumnNo Then
                    MaxColumnNo = CheckCol  ; Set new max col
                Else
                    ; Do Nothing
                EndIf
            EndIf
            i += 1  ; Next Row
        Loop
    Else
        ; Empty row, go on checking until maxemptyrows are reached, or a row with data is found
        i += 1
        EmptyRowsCount += 1
    EndIf
Loop

 

Edited October 22, 2020 by GokAy
Added some description to 1st code, and UsedRange definition

[seadoggie01]

Row.cell.count counts the number of cells in the row, not the number of cells with data like the formula =Count(1:1)  Row.Cell.Count returns either 2^8 (xls) or 2^16 (xlsx)

One issue with using .UsedRange is that it doesn't always return the "correct" range as being used... I'm unsure of the circumstances, but I remember having issues with this and .SpecialCells(xlCellTypeLastCell) returning the wrong cells occasionally.

Also, Siwa mentioned that they wanted the last used cell in a particular column (if I read correctly). Using .UsedRange would return the last used column in the entire sheet, but it might not be the last column the that particular row. Like if you have data in A1 and B2 and call .UsedRange.Columns.Count it would return 2 instead of the expected 1 for Row 1.

[GokAy]

Yeah, I am not that used to using UsedRange as well due to this nature, but then never needed it for my purposes. My 1st code, goes through all rows in used range though and finds the max column no for each. Then either you can keep an array for each row, or make one max col variable and update it.

He started with only the last used cell but conversation somehow changed to having empty rows. So not sure what is required atm. Maybe I am understanding it all wrong

[Siwa]

This is why I love autoit community. Everybody try's their best to solve another members problem and they share everything. Thanks guys. 

 

22 hours ago, water said:

300 x 31 isn't too big. Reading all cells into an array with _Excel_RangeRead is much faster thean reading every cell.
Looping through the array will then just access the array in memory.

If the H.D.D is an old one ( like the one we have at work ( 5400 rpms) ), it takes about 20 to 25 seconds to read entire rows.

9 hours ago, seadoggie01 said:

There are tons of examples of how to do this. It really depends on your data, but I've found this to be the best method. I usually use something like:

Func LastUsedColumnInRow($oSheet, $iRow) ; Requires <Excel.au3>

    If Not ObjName($oSheet) = "_Worksheet" Then Return SetError(1, 1, False) ; Not a worksheet object
    If Not IsInt($iRow) Then Return SetError(1, 2, False) ; Bad parameter
    Local $oErr = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oErr
    ; Get the number of cells in a single row (aka the number of columns - works with xls and xlsx)
    Local $iColumnCount = $oSheet.Cells($iRow, 1).EntireRow.Cells.Count
    If @error Then Return SetError(2, 0, False) ; Invalid row
    ; Not defined in Excel.au3 and I don't like magic numbers
    Local $xlToLeft = -4159
    ; Get the last cell in the row and move left
    Local $iColumn = $oSheet.Cells($iRow, $iColumnCount).End($xlToLeft).Column
    If @error Then Return SetError(3, 0, False) ; I don't think this will ever happen

    Return $iColumn

EndFunc

I don't have enough time to test it now, but It seems this one is a good example too. Most definitely I'll use I in the future projects.

 

@GokAy I only needed a range of the rows to get he values from. because of some formulas at the end of each row, I don't need the entire row too.

 

I did came up with this code for my problem last night. 

With $oWorkbook.Sheets(3)
    $lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
    EndWith

Which I have a FOR ... NEXT statement for $i .

And finally I decided to give the array solution a try, and it was much much faster than before.

#include <Array.au3>
#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\99.xlsx",False,True)
Global $Total_Rows = _Excel_RangeRead($oWorkbook,2, $oWorkbook.Worksheets(2).Usedrange.Columns("C:C"),1)
If @error Then  MsgBox(BitOR($MB_ICONERROR,$MB_TOPMOST),"Error in reading","Error in reading the excel file")
Global $Total_Rows_Count = Ubound($Total_Rows)
Global $aNames = _Excel_RangeRead($oWorkbook, 2, "B2:C"&Ubound($Total_Rows), 1)


$Total_Rows = _Excel_RangeRead($oWorkbook,2, $oWorkbook.Worksheets(2).Usedrange.Columns("C:C"),1)
$oData = _Excel_RangeRead($oWorkbook, 3, "I2:AM"&Ubound($Total_Rows), 1)

For $i = 0 To UBound($aNames) - 1

    For $l = UBound($oData,2)-1 To 0 Step -1
        If $oData[$i][$l]>0 Then
            $Data = $oData[$i][$l]
            ConsoleWrite(@CRLF & "For the row number : " & $i+1 & " , the column for the last value is : " & $l+1 & ", and the valuse is = "& $Data & @CRLF)
            ExitLoop
        EndIf
    Next


Next

 

[GokAy]

41 minutes ago, Siwa said:

I did came up with this code for my problem last night. 

With $oWorkbook.Sheets(3)
    $lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
    EndWith

 

The problem with this one is, you won't catch a row that has no empty cells on it's columns. So add +1 to the column count.

Additional Notes: (I am more proficient in Excel VBA than AutoIt, so not sure if these are valid here. Please consider them as informational rather than a solution to your case). 

In Excel you don't need to get a range into an array to get it's row/column counts. Considering you can select the range without needing to set a range (VBA stuff), you can issue "selection.rows.count" which will return the count. So it becomes:

1. Select the range: UsedRange.Columns("C:C").Select

2. Get row count: Selection.Rows.Count

3. Additionally if you need the starting row of range: Selection.Row

Adding the $oData ("I2:AM" & ..) bit to this: You don't need to count the rows even

- If you know the number of columns and which column they start at for the ultimate range you want (in 1 step). Numbers used are just as an example:

UsedRange.Columns("C:C").Resize(,5).Offset(0,6).Select

Here, Resize(,5) means you don't want to resize rows, and resize columns to 5

Offset(0,6) means offset the range by 0 rows (+ is downward) and +6 columns ( + is towards right)

These value are with respect to the upper leftmost cell of selection/range.

And another last option is to not select it at all (remember you don't need to select, maybe AutoIt requires it, idk). Also, if UsedRange is enough for rows data, then you don't even need Columns("C:C") part. Since UsedRange rows won't change however column you select.

Array = UsedRange.Resize(,5).Offset(0,6) - (here offset number for column should change since now you are offsetting from the start column rather than C). Get your array in 1 line of code, and it is fast.

And if you can get this into an array in AutoIt with _ExcelRangeRead that would be golden.

[water]

23 hours ago, GokAy said:

Considering you can select the range without needing to set a range (VBA stuff), you can issue "selection.rows.count" which will return the count. So it becomes:

1. Select the range: UsedRange.Columns("C:C").Select

I'm not sure I fully understand what you try to tell us
With your code you are working with a range implicitly but do not store the range object in a variable.

I do not recommend to work with a selection because it just displays the range on the screen. Hence it might interfere with what the user tries to do.
That's why I recommend to work with ranges where possible.