Excel - Find empty cell

[Queener]

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.

[water]

Maybe this helps: https://www.autoitscript.com/wiki/Excel_Range

[Queener]

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 March 16, 2016 by Queener

[water]

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

[Queener]

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 March 17, 2016 by Queener

[water]

Either a bug or WAD

[nico18n]

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:

 

 

 

[water]

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.

[nico18n]

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 March 29, 2016 by nico18n

[water]

Maybe this thread sheds some light on the subject: https://www.add-in-express.com/creating-addins-blog/2013/12/06/find-last-used-cell-excel/

[nico18n]

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 March 29, 2016 by nico18n