Sign in to follow this  
Followers 0
Bearpocalypse

Getting last cell used + 1 frustration

3 posts in this topic

We have a spreadsheet that tracks hardware that has or will be destroyed. Said spreadsheet is currently at 2863 rows. I've been messing around with some automation to remove the people problem element from this process and I have everything sorted out except getting the next unused cell to activate before writing data. Since the spreadsheet is rather large, I don't want to read it into an array. I have been using

$newRow = $oExcel.ActiveCell.Row
$newRow = $oExcel.ActiveCell.Row + 1

This works like a charm, IF the last cell used is where my script has left off. Example, if I run the script and data gets written to it and it ends at row 2864 and column I and then the spreadsheet saves, everything is all good. I tested this out and was able to write to each new row. But if I clicked on a different row and saved, then the row below it would be overwritten with data. Say I selected D:40 and saved the sheet. If I then ran my script, A:41 would be the next in line to be written to, not (just for semi-simplicity based on the next empty row being 2863) A:2864.

I have trolled over the internet and the forums here and have tried different things. I saw someone use

$newRow = $oRange.SpecialCells($xlCellTypeLastCell).Activate

But alas, that did not work for me.

An example of what I am doing is:

;Sets up the Excel sheet.
$oExcel = ObjCreate("Excel.Application")
$oBook = $oExcel.Workbooks.Open("SomeWillyNillyThing.xlsx")
$oExcel.Visible = True
$oSheet = $oBook.Worksheets(1)
$oSheet.Activate
$oRange = $oSheet.UsedRange

$newRow = $oExcel.ActiveCell.Row
$newRow = $oExcel.ActiveCell.Row + 1

$oExcel.Range("A" & $newRow).Activate
    With $oExcel
        .Range('A' & $newRow).Interior.Color = 65535
        .Range('A' & $newRow).Borders.LineStyle = $xlContinuous
        .range('A' & $newRow).Borders($xlEdgeBottom).LineStyle = $xlContinuous
        .range('A' & $newRow).Borders($xlEdgetop).LineStyle = $xlContinuous
        .range('A' & $newRow).Borders($xlEdgeright).LineStyle = $xlContinuous
        .range('A' & $newRow).Borders($xlEdgeLeft).LineStyle = $xlContinuous
    EndWith

_ExcelWriteCell($oExcel, 'My Data Here', $newRow, 1)

This goes on checking different variables and writing data. It's not the most efficient way, but I'm wanting to make it work before I make it efficient.

Any help is appreciated. Please let me know if I need to clarify anything.

Share this post


Link to post
Share on other sites



If all rows starting with row one are filled you could use "UsedRange" to get the last used row.

$iLastUsedRow = $oSheet.UsedRange.Rows.Count

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-02-03 - Version 1.4.7.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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I figured it out! All it took was making a thread about it for me the little light bulb to go CLICK!

I created a new spreadsheet and copied over the data to it. That way, ALL 65000 are not formatted. Then suddenly this code magically worked.

$oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row + 1

And thank you Water, I came across a reply you had made about the formatting of unused cells screwing things up.

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
Sign in to follow this  
Followers 0