davidimurray

Excel Range - Find Bottom

3 posts in this topic

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)

 

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:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
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)
EndWith

 

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