Excel gets the position and value of the last non-empty cell in column D

[ekko]

(defun c:test
	    (/	       xlApp	 xlBook	   xlSheet   atsheet
	     columnD   Columnall row	   col	     cellValue
	    )
  (vl-load-com)
  (setq xlApp (vlax-get-or-create-object "Excel.Application"))

  (setq
    xlBook  (vlax-get-property xlApp 'Sheets)
    xlSheet (vlax-get-property xlBook 'Item "Sheet1")
    atsheet (vlax-invoke-method xlSheet 'Activate)
  )
  (setq ColumnD (vlax-get-property xlSheet 'Range "D:D"))

  ;|(setq columnall (vlax-get-property xlSheet 'Columns))
  (setq	columnD
	 (vlax-get-property
	   (vlax-variant-value
	     (vlax-get-property columnall 'Item 4)
	   )
	   'EntireColumn
	 )
  )|;


  (setq	lastCell (vlax-invoke
		   ColumnD 'Find "*" nil xlValues xlByColumns xlNext)
  )


  (if lastCell
    (progn
      (setq row (vlax-get lastCell 'Row))
      (setq col (vlax-get lastCell 'Column))
      (setq cellValue (vlax-get lastCell 'Value))
      (if (numberp cellValue)
	(setq cellValue (rtos cellValue 2 2))
      )
      (princ (strcat "Last non-empty cell in column D is in row "
		     (itoa row)
		     ", column "
		     (itoa col)
		     ", with value: "
		     "\""
		     cellValue
		     "\""
	     )
      )
    )
    (princ "No non-empty cell found in column D")
  )
  (vlax-release-object columnD)
  (vlax-release-object lastCell)
  (vlax-release-object xlApp)
  (gc)
  (gc)

  (princ)
)

 

Hello everyone, I am trying to get the value and position of the non-empty cell in the last row of column D, but (vlax-invoke ColumnD 'Find "*" nil xlValues xlByColumns xlNext) this method does not seem to work, I have tried for a long time, but I still can't find it The reason is that I can only go to the forum to ask for help from friends As shown in the figure: D10 is its position, and its value is "last"    test.xlsx

 

[hosneyalaa]

See this

 

https://www.cadtutor.net/forum/topic/68220-how-to-know-the-latest-cell-number-in-excel-lisp/

[Stefan BMR]

Try this.

I'm not sure I caught all possible errors, please test it in different scenarios.

Ah yes, I took the lazy route of making sure the range value is always a 2-dimensional array. When the range is a single cell, the Value property returns its contents.

(defun c:DCOL ( / variantvalue msg excel sheet range rows lst)
  
  (defun VariantValue (x / var)
    (cond
      ((not x) nil)
      ((eq (type x) 'safearray)
         (VariantValue (vlax-safearray->list x))
      )
      ((eq (type x) 'variant)
       (if
         (vl-catch-all-error-p
           (setq var (vl-catch-all-apply 'vlax-variant-value (list x)))
         )
         nil
         (VariantValue var)
       )
      )
      ((listp x) (mapcar 'VariantValue x))
      (T x)
    )
  )
  
  (if
    (and
      (setq msg   "\nExcel is not open.")
      (setq excel (vlax-get-object "Excel.Application"))
      (setq msg   "\nNo active sheet found.")
      (setq sheet (vlax-get excel "ActiveSheet"))
      (setq range (vlax-get sheet "UsedRange"))
      (setq rows  (vlax-get (vlax-get range "Rows") "Count"))
      (setq range (vlax-get-property sheet "Range" (strcat "D1:D" (itoa rows))))
      (setq msg   "\nD column is empty.")
      (setq lst   (variantValue (vlax-get-property range "value")))
    )
    (progn
      (if
        (listp lst)
        (setq lst (reverse lst))
        (setq lst (list (list lst)))
      )
      (while
        (and lst (not (caar lst)))
        (setq lst (cdr lst))
      )
      (if lst
        (progn
          (princ "\nD")
          (princ (length lst))
          (princ " - ")
          (princ (caar lst))
        )
        (princ "\nD column is empty.")
      )
    )
    (if msg (princ msg))
  )
  (princ)
)

[BIGAL]

Another way not tested is to use Getcell and just walk down the column till you get a nil value as a safe guard, can get range of current sheet.

[ekko]

@hosneyalaa @Stefan BMR The very good ideas provided by the two, I tried it, and it perfectly fulfilled my needs. But I still want to find out the cause of the error. About (setq lastCell (vlax-invoke
ColumnD 'Find "*" nil xlValues xlByColumns xlNext)
   ) Why can’t this code achieve this function? Is my method of use wrong? Who can help me solve this problem?

[ekko]

12 hours ago, BIGAL said:

Another way not tested is to use Getcell and just walk down the column till you get a nil value as a safe guard, can get range of current sheet.

If every other cell is Nil value, or multiple cells are Nil value, is this method still applicable?Maybe I need to provide a cell endpoint for the loop?

Edited September 7, 2023 by ekko

[exceed]

 

 

; Find Last Row/Column in Excel - 2023.09.08 exceed
; command
; FLRE = find last row
; FLCE = find last column

(defun c:FLRE ()
  (ex:FLRCE "R")
)

(defun c:FLCE ()
  (ex:FLRCE "C")
)

(defun ex:FLRCE ( rc / xlApp xlBook xlSheet AcSheet Cells xlcols xlrows 
                 Maxcols MaxRows ColumnInput RowInput
                 ColumnInt addr Rng finddirection whatyoufind 
                 whatyoufindrow whatyoufindcol 
                 whatyoufindtxt
                ) 
  (vl-load-com)
  ;-------------------------------------------------------------------------------
  ; Number2Alpha - Converts Number into Alpha string
  ; Function By: Gilles Chanteau from Marseille, France
  ; Arguments: 1
  ;   Num# = Number to convert
  ; Syntax example: (Number2Alpha 731) = "ABC"
  ;-------------------------------------------------------------------------------
  (defun Number2Alpha (Num# / Val#) 
    (if (< Num# 27) 
      (chr (+ 64 Num#))
      (if (= 0 (setq Val# (rem Num# 26))) 
        (strcat (Number2Alpha (1- (/ Num# 26))) "Z")
        (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
      )
    )
  )
  ; Alpha2Number - Converts Alpha string into Number
  ; Function By: Gilles Chanteau from Marseille, France
  ; Arguments: 1
  ;   Str$ = String to convert
  ; Syntax example: (Alpha2Number "ABC") = 731
  ;-------------------------------------------------------------------------------
  (defun Alpha2Number (Str$ / Num#) 
    (if (= 0 (setq Num# (strlen Str$))) 
      0
      (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#))) 
         (Alpha2Number (substr Str$ 2))
      )
    )
  )

  (setq xlApp (vlax-get-or-create-object "Excel.Application"))
  (setq xlBook (vlax-get-property xlApp 'Workbooks))
  (setq xlSheet (vlax-get-property xlApp 'Sheets))
  (setq AcSheet (vlax-get-property xlApp 'ActiveSheet))
  (setq Cells (vlax-get-property AcSheet 'Cells))
  (setq xlcols (vlax-get-property AcSheet 'Columns))
  (setq xlrows (vlax-get-property AcSheet 'Rows))
  (setq MaxCols (vlax-get-property xlcols 'Count))
  (setq MaxRows (vlax-get-property xlrows 'Count))
  
  (cond
    ((= rc "R")
      (setq ColumnInput (getstring "\n Enter the Column Number to find the last Row (A,B,C.. or 1,2,3..) : "))
      (setq ColumnInt (atoi ColumnInput))
      (if (and (= ColumnInt 0) (/= CoumnInput "0")) 
        (progn
          (setq ColumnInput (strcase ColumnInput))
        )
        (progn 
          (setq ColumnInput (Number2Alpha ColumnInt))
        )
      )
      (setq addr (strcat ColumnInput (itoa MaxRows)))
      (setq finddirection 3)
    )
    ((= rc "C")
      (setq RowInput (getint "\n Enter the Row Number to find the last Column (1,2,3..) : "))  
      (setq addr (strcat (Number2Alpha MaxCols) (itoa RowInput)))
      (setq finddirection 1)
    )
  )
  (setq Rng (vlax-get-property AcSheet 'Range addr))

  (setq whatyoufind (vlax-get-property Rng 'End finddirection)) ; finddirection : 1 = xlToLeft, 2 = xlToRight, 3 = xlUp, 4 = xlDown
  (setq whatyoufindrow (vlax-get-property whatyoufind 'row))
  (setq whatyoufindcol (vlax-get-property whatyoufind 'column))
  (setq whatyoufindtxt (vlax-variant-value (vlax-get-property whatyoufind 'value)))
  (if (= whatyoufindtxt nil) (setq whatyoufindtxt ""))
  (vlax-invoke whatyoufind 'select) ;If you don't need to select a cell, delete this line

  (cond 
    ((= rc "R")
      (princ "Last non-empty cell in column [ ")
      (princ ColumnInput) 
      (princ " (= ")
      (princ (Alpha2Number ColumnInput))
      (princ " ) ] is in row [ ")
      (princ whatyoufindrow)
    )
    ((= rc "C")
      (princ "Last non-empty cell in Row [ ")
      (princ RowInput)  
      (princ " ] is in column [ ")
      (princ (Number2Alpha whatyoufindcol))
      (princ " (= ")
      (princ whatyoufindcol)
      (princ " )")
    )
  )
  (princ " ], with value : [ ")
  (princ whatyoufindtxt)
  (princ " ].")

  (vlax-release-object xlApp)
  (princ)
)

 

command = FLRE 

then input column number by number or alphabet

 

Edited September 8, 2023 by exceed
add find last column function

[exceed]

 

23 hours ago, ekko said:

@hosneyalaa @Stefan BMR The very good ideas provided by the two, I tried it, and it perfectly fulfilled my needs. But I still want to find out the cause of the error. About (setq lastCell (vlax-invoke
ColumnD 'Find "*" nil xlValues xlByColumns xlNext)
   ) Why can’t this code achieve this function? Is my method of use wrong? Who can help me solve this problem?

 

(defun c:test
	    (/	       xlApp	 xlBook	   xlSheet   atsheet
	     columnD   Columnall row	   col	     cellValue
	    )
  (vl-load-com)
  (setq xlApp (vlax-get-or-create-object "Excel.Application"))

  (setq
    xlBook  (vlax-get-property xlApp 'Sheets)
    xlSheet (vlax-get-property xlBook 'Item "Sheet1")
    atsheet (vlax-invoke-method xlSheet 'Activate)
  )
  (setq ColumnD (vlax-get-property xlSheet 'Range "D:D"))

  (setq	lastCell (vlax-invoke
		   ColumnD 'Find 
                    "*" nil -4163 1 2 2) ; 'Fine what after lookin lookat SearchOrder SearchDirection
  )
  ; xlfindlookin > xlComments = -4144, xlCommentsThreaded = -4184, xlFormulas = -4123, xlValues = -4163
  ; xlLookAt > xlWhole = 1, xlPart = 2
  ; XlSearchOrder > xlByRows = 1, xlByColumns = 2
  ; XlSearchDirection > xlNext = 1, xlPrevious = 2

  (if lastCell
    (progn
      (setq row (vlax-get lastCell 'Row))
      (setq col (vlax-get lastCell 'Column))
      (setq cellValue (vlax-get lastCell 'Value))
      (if (numberp cellValue)
	(setq cellValue (rtos cellValue 2 2))
      )
      (princ (strcat "Last non-empty cell in column D is in row "
		     (itoa row)
		     ", column "
		     (itoa col)
		     ", with value: "
		     "\""
		     cellValue
		     "\""
	     )
      )
    )
    (princ "No non-empty cell found in column D")
  )
  (vlax-release-object columnD)
  (vlax-release-object lastCell)
  (vlax-release-object xlApp)
  (gc)
  (gc)

  (princ)
)

1.

If you want to use this, you must change all values starting with xl~ to constants.

Because that is a value known to excel only. not a value known to autolisp. 

Just think of it like this. 

(setq xlValue -4163) 

(setq xlWhole 1)

..... etc

excel vba has this values in those variables already

you can find it in microsoft pages. like this link

https://learn.microsoft.com/en-us/office/vba/api/excel.xlfindlookin

 

2.

there are two ways to find the last cell.

 

forward way

as you mentioned, if the middle of the list is empty,

trapping this requires knowing the total row count, 

If there are cells with values in the remaining cells performing it again, loop this

It's not reasonable.

and need to find empty cell "" instead of "*".

but you cannot enter the value nil or "" as the "what" argument of the excel find function.

can't put (strcat (chr 34) (chr 34)) or "\"\"" or :vlax-false also.

by vba can find "" but by autolisp cannot. as far as i know

 

Therefore, if you implement this, you have to search for the cell value with "*" and collect more data, so it is inefficient.

 

reverse way

changes the search direction. it's the universal way to find the last cell.

  (setq	lastCell (vlax-invoke
		   ColumnD 'Find 
                    "*" nil -4163 1 2 2) ; 'Find what after lookin lookat SearchOrder SearchDirection

 

Edited September 8, 2023 by exceed

[ekko]

8 hours ago, exceed said:

 

 

(defun c:test
	    (/	       xlApp	 xlBook	   xlSheet   atsheet
	     columnD   Columnall row	   col	     cellValue
	    )
  (vl-load-com)
  (setq xlApp (vlax-get-or-create-object "Excel.Application"))

  (setq
    xlBook  (vlax-get-property xlApp 'Sheets)
    xlSheet (vlax-get-property xlBook 'Item "Sheet1")
    atsheet (vlax-invoke-method xlSheet 'Activate)
  )
  (setq ColumnD (vlax-get-property xlSheet 'Range "D:D"))

  (setq	lastCell (vlax-invoke
		   ColumnD 'Find 
                    "*" nil -4163 1 2 2) ; 'Fine what after lookin lookat SearchOrder SearchDirection
  )
  ; xlfindlookin > xlComments = -4144, xlCommentsThreaded = -4184, xlFormulas = -4123, xlValues = -4163
  ; xlLookAt > xlWhole = 1, xlPart = 2
  ; XlSearchOrder > xlByRows = 1, xlByColumns = 2
  ; XlSearchDirection > xlNext = 1, xlPrevious = 2

  (if lastCell
    (progn
      (setq row (vlax-get lastCell 'Row))
      (setq col (vlax-get lastCell 'Column))
      (setq cellValue (vlax-get lastCell 'Value))
      (if (numberp cellValue)
	(setq cellValue (rtos cellValue 2 2))
      )
      (princ (strcat "Last non-empty cell in column D is in row "
		     (itoa row)
		     ", column "
		     (itoa col)
		     ", with value: "
		     "\""
		     cellValue
		     "\""
	     )
      )
    )
    (princ "No non-empty cell found in column D")
  )
  (vlax-release-object columnD)
  (vlax-release-object lastCell)
  (vlax-release-object xlApp)
  (gc)
  (gc)

  (princ)
)

1.

If you want to use this, you must change all values starting with xl~ to constants.

Because that is a value known to excel only. not a value known to autolisp. 

Just think of it like this. 

(setq xlValue -4163) 

(setq xlWhole 1)

..... etc

 

2.

there are two ways to find the last cell.

 

forward way

as you mentioned, if the middle of the list is empty,

trapping this requires knowing the total row count, 

If there are cells with values in the remaining cells performing it again, loop this

It's not reasonable.

and need to find empty cell "" instead of "*".

but you cannot enter the value nil or "" as the "what" argument of the excel find function.

can't put (strcat (chr 34) (chr 34)) or "\"\"" or :vlax-false also. 

 

Therefore, if you implement this, you have to search for the cell value with "*" and collect more data, so it is inefficient.

 

reverse way

changes the search direction. it's the universal way to find the last cell.

  (setq	lastCell (vlax-invoke
		   ColumnD 'Find 
                    "*" nil -4163 1 2 2) ; 'Fine what after lookin lookat SearchOrder SearchDirection
  )

 

Thanks for your help, admire your ability to solve problems, thanks to your knowledge base