Jump to content
royalmarine

excel - find next available empty cell in a particular column

Recommended Posts

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 :/

Share this post


Link to post
Share on other sites
water

Excel provides the information about the last used cells.

I will provide an example tomorrow..

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by JLogan3o13
  • Like 1

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
royalmarine

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

Share this post


Link to post
Share on other sites
water

Glad you got it working :D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
JLogan3o13

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

 

 

Share this post


Link to post
Share on other sites
water

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

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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:

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
MrCheese

I ended up using:

 

 $oExcel.ActiveSheet.UsedRange.Rows.Count

to give me the last row used.

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
water

Why should it "fail" at B20?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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

×