get last empty row

[DigDeep]

Hi, In my Excel sheet, I want to exclude the 1st column header line. Which would be A1 and start reading from A2 onward.

if A2 or last line is empty then stop. Else read the A2 or last line and display result.

 

Local $oExcel = _Excel_Open(False)
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Temp\Test.xls")
$Lines = $oWorkbook.ActiveSheet.UsedRange.Rows.Count


For $k = $Lines To 2 Step 1
Local $Column = _Excel_RangeRead($oWorkbook, Default, "A" & $k)

If $Column = '' Then ; If A2 is blank
MsgBox(0, '', 'Blank cell.')

Else
MsgBox(0, '', $Column)

_Excel_RangeDelete($oWorkbook.ActiveSheet, $k & ":" & $k, 1)
Endif

Next
_Excel_Close($oExcel)

 

Edited January 9, 2017 by DigDeep

[Subz]

You need to Step backwards not forwards i.e.

For $k = $Lines to 2 Step - 1

 

[DigDeep]

Tried this one too.

In this case, if the Excel has any values / text from A2 - end of the column... then the below works correctly as it should.

Else
MsgBox(0, '', $Column)

_Excel_RangeDelete($oWorkbook.ActiveSheet, $k & ":" & $k, 1)

 

But the issue comes up if the excel does not has any data from A2 - end of the column. Which means $Lines would be 1 here. And in this case, it does not show the below result and EXIT.

If $Column = '' Then ; If A2 is blank
MsgBox(0, '', 'Blank cell.')

Attaching a test file here to check. You can then delete rows 2 - 5 and just keep row 1 and see that it does not show the msgbox as 'blank cell'.

My goal is to read the excel. If any data found from A & end of the column - A2, then display and take actions. If only row 1 exists with headers then mention as 'blank cells'.

It would be also a great idea if we can keep a loop in a way that if there is any data present > it will read and take actions > delete that row > until it comes to row2. Until it finds Row2 as blank.

Test.xls

Edited January 10, 2017 by DigDeep

[Subz]

You mean something like this?

#include <Excel.au3>
Local $Column
Local $oExcel = _Excel_Open(False)
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Temp\Test.xls")
$Lines = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
If $Lines <= 1 Then
    MsgBox(0, '', 'Blank cell.')
Else
    For $k = $Lines To 2 Step - 1
        $Column = _Excel_RangeRead($oWorkbook, Default, "A" & $k)
        If $Column = '' Then ; If A2 is blank
            MsgBox(0, '', 'Blank cell.')

        Else
            MsgBox(0, '', $Column)
            _Excel_RangeDelete($oWorkbook.ActiveSheet, $k & ":" & $k, 1)
        Endif
    Next
EndIf
_Excel_Close($oExcel)

 

[DigDeep]

Works all good in both the ways. Except just that in the 2nd part when it completes reading and deleting all rows until Row 2, it exits instead of saying 'Blank Cell'.

But I can still manage with this part too.

Thank you @Subz. Appreciate help.

[Subz]

No problem, you could just add the following line above Next:

If $k = 2 Then MsgBox('', 'Blank Cell')