Jump to content

Recommended Posts

Posted

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.")
Posted

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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.")
Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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.")
Posted

Either a bug or WAD :huh:

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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:

 

 

 

Posted

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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
Posted

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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

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
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...