Excel Range - Find Bottom

[davidimurray]

Hello everybody

I am new to AutoIt, having only discovered it a week ago but already I can tell t is a fantastic tool. I've already made a few little projects.

I am now looking at a project that copies information from a number of webpages (daily reports), copies them into excel and then summarises any items that have not been ticked off on a checklist.

So far I got the info extracted from the Internet explorer pages into a different sheet in excel for each day. My next challenge is to go through a column, find any 'No' and then copy that row to a new sheet. The problem is there are merged cells at the top and bottom of the rows I am interested in. The top row is always in the same position, but the last bottom row above the merged cell can vary. I am struggling to find where this bottom row is. I can select the top row and Ctrl Shift Down to select the range, but I cannot get the range returned.

Code I have tried is below.

Any help much appreciated

#include <IE.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Date.au3>

#include <Excel.au3>
Local $oExcel = _Excel_Open()

WinActivate("Spreadsheet example - Excel")

$oExcel.ActiveSheet.Range("B7").Select
Send("{SHIFTDOWN}{CTRLDOWN}{DOWN}{SHIFTUP}{CTRLUP}")

$oRange = $oExcel.Activesheet.Range("B7").CurrentRegion

$sUpperLeftCell = $oExcel.ActiveSheet.UsedRange.Cells(1, 1).Address

;$oRange = $oExcel.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)

MsgBox($MB_OK, "Report Result", $sUpperLeftCell)
MsgBox($MB_OK, "Report Result", $oRange)

 

[water]

Welcome to AutoIt and the forum!

This page in the wiki describes how to get the last used cell.

[davidimurray]

This worked beautifully thanks -

 

Local Const $xlUp = -4162
With $oExcel.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