excel - find next available empty cell in a particular column

[royalmarine]

Hey guys, been trying to find if it's possible to find the next empty cell in Excel.

I have a program that writes several rows of data, but I need it to find the next empty row to use.

I can search via a single column, which will do just fine, as I can ensure that no data exists in column 1 or column A.

Not sure if it's available in excel.au3 or if I would need to use water's alpha version.

Any suggestions?

Thanks!

p.s. I didn't provide any code as I have none as of yet, since I can't find anything to base some code off :/

[water]

Excel provides the information about the last used cells.

I will provide an example tomorrow..

[JLogan3o13]

I'm sure water can give you better using the excel object (I had that snippet but cannot find it), but for a quick and dirty you could do something like this:

#include <Excel.au3>
$oExcel = _ExcelBookOpen(@DesktopDir & "\Test.xls", 1)

For $i = 1 To 10
    $cell = _ExcelReadCell($oExcel, $i, 1)
        If $cell = "" Then
            _ExcelWriteCell($oExcel, "Found an empty cell", $i, 1)
            ExitLoop
        EndIf
Next

Edit: Found it. This will move the cursor to the first empty row in column A

Const $xlCellTypeLastCell = 11

$oExcel = ObjCreate("Excel.Application")
    $oBook = $oExcel.Workbooks.Open("C:\Users\Hades\Desktop\Test.xls")
    $oExcel.Visible = True
    $oSheet = $oBook.Worksheets(1)
    $oSheet.Activate

    $oRange = $oSheet.UsedRange
    $oRange.SpecialCells($xlCellTypeLastCell).Activate

    $newRow = $oExcel.ActiveCell.Row + 1
    $oExcel.Range("A" & $newRow).Activate

Edited May 16, 2013 by JLogan3o13

[royalmarine]

that for loop worked perfect for me. Thanks a mill guys

[water]

Glad you got it working

[JLogan3o13]

Glad that did it for you. If everything is resolved, you can mark the topic as solved.

[MrCheese]

Hey Guys,

I had a question about this one, but the solution above didn't help me.

I used an example in the range find function.

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

$test= "test"
$cBookLocalName = @ScriptDir & "\" & $test & ".xlsx"
Global $oWorkbook = 0
_Excel_BookClose($oWorkbook)
Global $oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, $cBookLocalName)
Local $aResult = _Excel_RangeFind($oWorkbook, "37000")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

I wanted to find "37000" in Column A.

But I get:

"C:\Users\60080462\Documents\Stuff\macros\autoit-v3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR

 

My excel file essentially has '1's in every cell from A1:C33, except for A27 which has 37000 (attached).

The end goal is to determine the first row containing "" (i.e. is blank) in column A, and then feed that as a variable for use elsewhere in the script.

As always, your help is appreciated! 

 

Thanks

 

 

test.xlsx

[MrCheese]

OK:

so this works, and gives me an array:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

$test= "test"
$cBookLocalName = @ScriptDir & "\" & $test & ".xlsx"
Global $oWorkbook = 0
_Excel_BookClose($oWorkbook)
Global $oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, $cBookLocalName)

$lastline=_Excel_RangeRead ($oWorkbook,Default,Default,Default)
_ArrayDisplay($lastline)

Thanks to here: 

However, how do get the variable out as the next row that is blank?

Thanks

 

 

[water]

The wiki describes how to retrieve the value of some special cells: https://www.autoitscript.com/wiki/Excel_Range

Edited November 1, 2016 by water

[water]

1 hour ago, MrCheese said:

"C:\Users\60080462\Documents\Stuff\macros\autoit-v3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR

That's caused by a bug in the implmentation of the COM error handling in the latest version of AutoIt.
The solution is described here:

 

[MrCheese]

I ended up using:

 

 $oExcel.ActiveSheet.UsedRange.Rows.Count

to give me the last row used.

[water]

But be careful: This is only true when the used range starts with row 1 - as described in the wiki.

[MrCheese]

yeah they usually will.

 

One thing though, do you know how to determine the usedrange on just a particular column.

Say, I have data A1:A50

and as the script roles, it puts a comment in column B.

But say at B20 it fails.

and I want the script to determine where it failed by determining the last row used in column B.

 

Thoughts?

[water]

Why should it "fail" at B20?