AutoHotKey - How to Get the Row Number of the Last Used Row in Column (From the Bottom Up)?

Question

Seems pretty straightforward, but am not having any success.

Pretty simple, using AHK, I want to get the number of the last row in a worksheet with a value in it, going from the bottom up. I can't go top down because some rows are blank, so has to be bottom up.

My code loops through all files in a selected folder (which only contain excel files), opens them, then tries to set the print area based on how many rows are used in the file. If it's <= 43 rows, set the print area to 43rd row, if it's more, set the print area to that row.

My code for test.ahk looks like this:

FileSelectFolder, WhichFolder  ; Ask the user to pick a folder.

Loop, %WhichFolder%\*.*
{

    Xl := ComObjActive("Excel.Application")
    Xl.Workbooks.open(A_LoopFileFullPath)       ; open workbook
    Xl.Visible := 1                             ; make Excel Application visible

    Xl.DisplayAlerts := False

    ; First check if it's the dumb file that opens first, not part of the folder
    if xl.ActiveWorkbook.Name = "test.ahk" {
        xl.Quit()
        continue
        }

    ; Wait for the workbook to open
    Sleep, 2000

    ; Always start on the first worksheet
    Xl.Worksheets(1).Activate

    ; Now find the last used row for the print area
    ; If the last used row is on page 1, set it to the 43rd row

    lastRow := xl.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    ;MsgBox, %lastRow%


    ; Set the print area
    if lastRow <= 43 {
        xl.ActiveSheet.PageSetup.PrintArea := "A1:K43" 
    else {
        xl.ActiveSheet.PageSetup.PrintArea := "A1:K"lastRow
    }



    ; Wait to close the script if you want for testing
    Sleep, 20000


    ; Close and save the workbook
    xl.ActiveWorkbook.Close(1)

    Xl.DisplayAlerts := True


}
ExitApp

...and I'm getting this error:

I've tried lots of other combinations of trying to get the last row with a value in it too, but have had no success. Any help would be greatly appreciated. Thanks.

https://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range Lots of things that could go wrong but ... one answer says indexing starts at 1. Is it possible there are no rows and you are asking Excel for `A0`? — ta.speot.is, Dec 23 '19 at 01:01
Also `Xl.Worksheets("FIRE EXT.")` appears in your error but not the code you posted. — ta.speot.is, Dec 23 '19 at 01:02

score 3 · Answer 1 · answered Dec 23 '19 at 01:15

3

UDPATE!

Found a working formula thanks to the help above:

xlUp := -4162
BottomRow := xl.Sheets(1).Rows.Count
LastRow1 := xl.Sheets(1).Range("B" BottomRow).End(xlUp).Row

answered Dec 23 '19 at 01:15

Matt Wilson

603
7
22

Did you mean `("B" & BottomRow)`? The ampersand seems to be missing. – Endre Both Dec 23 '19 at 04:42
@EndreBoth Negative. It works without the & sign. I'm not sure if the & throws an error or not, but without it, the code seems to tie "B" and then the number right after it nicely. Thanks! – Matt Wilson Dec 24 '19 at 00:04
Ah, apologies. I mixed up AHK and VBA syntax. – Endre Both Dec 24 '19 at 18:29

AutoHotKey - How to Get the Row Number of the Last Used Row in Column (From the Bottom Up)?

1 Answers1