Jump to content

Excel Range - Find Bottom

Recommended Posts


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")


$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)


Share this post

Link to post
Share on other sites

Welcome to AutoIt and the forum!

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

My UDFs and Tutorials:


Active Directory (NEW 2017-04-18 - Version - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version - Download - General Help & Support

ADO - Wiki


Share this post

Link to post
Share on other sites

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)


Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.