Jump to content

get last non-empty row - Excel


Jfish
 Share

Go to solution Solved by water,

Recommended Posts

I searched around for the best way to this and am still a bit lost.  I am trying to find the last non-empty cell in a column in Excel.  In the past, there was a function _ExcelSheetUsedRangeGet($oExcel, $vSheet) in >this thread.  However, much has changed since then and when I downloaded it I could not find that function to see how it worked.

Google found this site for me which offered a couple of promising looking approaches:

 

In any case, you should now use [All versions]:

Dim LastLine As Long 
LastLine = Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row
or :
Dim LastLine As Long 
LastLine = Range("A" & Rows.Count).End(xlUp).Row

They are in VBA.  I usually can convert VBA but I am not 100% on these.  The first approach looks a lot like it should convert easily to _Excel_RangeFind but I am not familiar enough with all the parameters.  I understand the operators referenced in the help file but I did not see a lot of info on XLFindLookIn and not sure what to do with .Row.

I am also not sure if these are the best approaches?  Any guidance on the best way to do this would be much appreciated.  If this is already part of _Excel_RangeFind I could use an example.  Any help would be greatly appreciated.

JFish

 

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to comment
Share on other sites

  • Solution

Use function _Excel_RangeRead and set parameter $vRange = Default to only read the used cells of a worksheet.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

@Water -

Thanks!  I can see the array and the size of the array is what I need.  Seems fast too - I only have 567 rows in the test file but I expect a lot more.  How does the performance compare to the prior _ExcelReadSheetToArray function?  Just curious.  Also, here is what I did in case anyone searches this thread:

#include <Array.au3>
#include<Excel.au3>
Local $oAppl = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\test.xlsx"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook, Default, Default, True)
$lastline=_Excel_RangeRead ($oWorkbook,Default,Default,Default)
_ArrayDisplay($lastline)

JFish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to comment
Share on other sites

The new function is about 20 to 100 times faster.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

@water -  WOW.  That prompts another question... there must be a more efficient way to delete all blank rows than what I am doing?

for $a=UBound($lastline) to 1 step -1
    $range="A"&$a
    $rowValue=_Excel_RangeRead($oWorkbook,Default,$range)
    if $rowValue=="" Then
        $delete=_Excel_RangeDelete($oWorkbook.ActiveSheet,$range,default,1)
    EndIf
Next

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to comment
Share on other sites

Why read each cell in the loop again? Check the value in the array $lastline and if blank delete the appropriate cell in the worksheet.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Please define "best" ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Not that I know of. But maybe Google shows some faster ways?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Example:

Start the Excel macro recorder, do as described here (http://www.theexceladdict.com/_t/t031008.htm) and translate the VBA code to AutoIt.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Add

#include <Debug.au3>
_DebugSetup()
_DebugCOMError()

at the top of your script to get more defailed error information.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Thx. 

 

"SpecialCells method of Range class failed"

xlCellTypeBlanks is the right type of special cell.  Do I have the syntax for that wrong?

EDIT: I refined the range to the last used cell and now have this error:

 

@@ DEBUG COM Error encountered in deleteblankrows.au3 (19) :
    Number            = 0x80020009 (-2147352567)
    WinDescription    = Exception occurred.
    Description       =
    Source            =
    HelpFile          =
    HelpContext       = 0
    LastDllError      = 0
    Retcode           = 0x800A03EC
>>>>>> Please close the "Report Log Window" to exit <<<<<<<

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to comment
Share on other sites

The cell type needs to be numeric. Please give this code a try:

Global Const $xlCellTypeBlanks = 4
$oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

@Water -

That did not work because it said "cannot re-declare a constant" for 

Global Const $xlCellTypeBlanks = 4

but this did work!:

$oWorkbook.ActiveSheet.Columns("A:A").SpecialCells(4).EntireRow.Delete

That's pretty cool!  :thumbsup:   Thanks very much for your help.  I know that was a bit of an investment in time on your side.  Also, I respectfully submit that this could be a cool UDF feature.

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to comment
Share on other sites

Then the constant is already declared in the UDF.

So the following line would be enough. Using the constant makes the code much easier to read and understand when you look into it in a few months ;)

$oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

×
×
  • Create New...