Queener

Excel - Find empty cell

11 posts in this topic

I did a search and could not find usedrange to find the first empty cell reading from A2 to A200. And if found; return the cell address. I search on the help file and could not come up with a solution to do so.

 

Any help is much appreciated.


Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites




My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#3 ·  Posted (edited)

awesome... thats helpful... Thanks water!

 

 

EDIT:

 

Quick question, how do you use it? I copy and paste onto my script and run, but nothing shows up with/without msgbox (0, "", $oRange).

Edited by Queener

Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites

To retrieve the address of the last used cell in column A use

$oRange = $oWorkbook.ActiveSheet.Range("A:A").SpecialCells($xlCellTypeLastCell)
$sAddress = $oRange.Address

To get the address of the first empty cell in column A simply add +1 to the above range:

$oRange = $oWorkbook.ActiveSheet.Range("A:A").SpecialCells($xlCellTypeLastCell).Offset(1)
$sAddress = $oRange.Address

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#5 ·  Posted (edited)

wow, thats like you're running the actual macro on autoit... correct me if I'm wrong.

 

btw, using that code; I get $E$19 eventhough Range is A:A. There are datas on A1 through E19, but since this is focusing on A column, I would think it populate the address based on A column only. Just incase this is the cause; A1 through E19 is a table name Asset.

 

EDIT: so I just add this and it gives me the right address

$oRange = $oWorkbook.ActiveSheet.Range("A:A").SpecialCells($xlCellTypeLastCell).Offset(1,-4)
$Address = $oRange.Address

 

Edited by Queener

Msgbox(0, "Hate", "Just hate it when I post a question and find my own answer after a couple tries. But if I don't post the question, I can't seem to resolve it at all.")

Share this post


Link to post
Share on other sites

Either a bug or WAD :huh:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

Hello,

Unfortunately still it does not work. What is the solution please? Thank you very very much.

 

On 17/3/2016 at 8:42 AM, Queener said:

 

 

 

Share this post


Link to post
Share on other sites

Are you sure you posted in the correct thread?
I can't see a post where you describe what doesn't work and what you have tried so far.
BTW: Your reference to a post by Queener is empty.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#9 ·  Posted (edited)

Hello Water,

I'm sorry if I was not clear. I have the same problem described by Queener. I want to find the first empty cell in the column A, but this I can not have her. The result is always the first empty cell in a column that has more filled cells. Example: Column A is the first empty cell to the line 120, the K column is the first blank cell in row 130, if I do the search using the command:
 

$oRange = $oWorkbook.ActiveSheet.Range("A1:A5000").SpecialCells($xlCellTypeLastCell).Offset(1,-4)
$Address = $oRange.Address

I always get the column position K. How do I find the first empty cell in the column A?
I thank you for your kind interest.

 

Edited by nico18n

Share this post


Link to post
Share on other sites

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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

#11 ·  Posted (edited)

Hello Water,

Thanks for the link, very interesting. However given that the size of the sheet are not very big, I thought I'd use this other method for me easier.

$kk=_Excel_RangeRead($oWorkbook,1,"A1:A4000") ;read the range in a array $kk
$trovato = _ArraySearch($kk,"") ;find the first empty
If @error Then

......

It will not be the best, but it works.

 

Edited by nico18n

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