Retreive LAST USED ROW / COL on Excel

[graves]

Hi,
Excel can retreive LAST USED ROW on a COLUMN only if every cell on this column have data. Also it can retreive last used column on a determined row only if every cell on this row have data.

Otherwise it returns the first unused cell on this column/row. (It emulates the Ctrl-Arrows keys)

Code: Select all

;-------------------------------------------------------------------------------
; Retreive LAST USED ROW/COL on excel
;-------------------------------------------------------------------------------

IncludeFile "COMate.pbi"
Define.COMateObject: Global ApplObj, ExcelBook, ExcelSheet, RangeObj: Define.i

Procedure Find_LastUsed(rowcol.s)
  lused.l = 0
  range.s = ""
  toend.s = ""
  value.s = ""

  if val(rowcol)           ; last used column (rowcol contains a numeric row)
    range = "Range('A"+rowcol+"')"
    toend = "End(-4161)\Activate()"
    value = "ActiveCell\Column"
  else                     ; last used row (rowcol contains a non-numeric column)
    range = "Range('"+rowcol+"1')"
    toend = "End(-4121)\Activate()"
    value = "ActiveCell\Row"
  endif

  RangeObj = ApplObj\GetObjectProperty(range)
  if COMate_GetLastErrorCode() = #S_OK
    RangeObj\Invoke(toend)
    lused = ApplObj\GetIntegerProperty(value)
    RangeObj\Release()
  endif
  ProcedureReturn lused
EndProcedure

ApplObj = COMate_CreateObject("Excel.Application")
if COMate_GetLastErrorCode() = #S_OK
  ApplObj\SetProperty("Visible = #False")

; excel book must exists before test. Use complete path
  ExcelBook = ApplObj\GetObjectProperty("Workbooks\Open('C:\ExcelSamples\SampleLUR.xls')")

  if COMate_GetLastErrorCode() = #S_OK
    ExcelBook\Invoke("Activate()")

    ExcelSheet = ApplObj\GetObjectProperty("Sheets(1)")
    if COMate_GetLastErrorCode() = #S_OK
      ExcelSheet\Invoke("Activate()")

      setrow.s = "Cells("+str(Find_LastUsed("A")+1)+",1) = 'next row'"         ; last row used on column A
      ApplObj\SetProperty(setrow)

      setcol.s = "Cells(1,"+str(Find_LastUsed("1")+1)+") = 'next col'"         ; last col used on row 1
      ApplObj\SetProperty(setcol)

      ExcelBook\Invoke("Save()")
      ApplObj\Invoke("Quit()")
      ExcelBook\Release()
      ApplObj\Release()
    else
      MessageRequester("ERROR", "Cannot open SHEET 1 on book")
    endif
  else
    MessageRequester("ERROR", "Cannot open EXCEL book")
  endif
else
  MessageRequester("ERROR", "Cannot open EXCEL application")
endif
END

[Michael Vogel]

You could use also Excel's array functions to get the same results

Code: Select all

{=MAX((1:1<>"")*COLUMN(1:1))}
{=MAX((A1:A9999<>"")*ROW(1:9999))}

Michael