DigDeep

get last empty row

6 posts in this topic

#1 ·  Posted (edited)

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 by DigDeep

Share this post


Link to post
Share on other sites



You need to Step backwards not forwards i.e.

For $k = $Lines to 2 Step - 1

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by DigDeep

Share this post


Link to post
Share on other sites

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)

 

1 person likes this

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

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

 

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