get last non-empty row - Excel

[Jfish]

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

 

[Radiance]

Hi,

I'm using _ExcelReadSheetToArray to read the whole file.

$return[0][0] is usually the last non-empty row.

[Jfish]

@radiance - thanks.  I know about that way but that is a deprecated function and will take up a lot of memory / time on a large sheet.

[water]

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

[Jfish]

@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

[water]

The new function is about 20 to 100 times faster.

[Jfish]

@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

[water]

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.

[Jfish]

Not enough coffee this AM that's why ... if that is removed - is looping the array the best approach?

[water]

Please define "best"

[Jfish]

Best: "Fastest"

[water]

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

[water]

Example:

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

[Jfish]

@Water -

Thanks!  I will look at that.

JFish

[Jfish]

@Water -

I think it should be:

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

but it does not seem to work.  Any suggestions?

[water]

Add

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

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

[Jfish]

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 October 30, 2014 by Jfish

[water]

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

[Jfish]

@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!     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.

[water]

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