1

Have written a succesful subroutine for data looking like:

enter image description here

(The list will continually be added to and length will change)

Public Function GetNewID() As Long 
    GetNewID = 1 + WorksheetFunction.Max(shList.Range("A2").CurrentRegion.Columns(1))
End Function

However, I really need data in the cells to always display the "ID-" text in front of the number (the additional characters are an important interface in multiple other areas of the broader program) and so need to parse out the "ID-" characters and determine the max of the remaining numbers. Fortunately the "ID-" will always be the same, 3 characters.

enter image description here

Tried the worksheetFunction.replace to replace "ID-" with "", and it worked, but it visibly replaces the data in the cells (want to maintain the visible text as "ID-XXX", there's some listboxes and similar its called into).

I guess I could write a loop and use LEFT/RIGHT to scrub all the values of their "ID-" and store those in a temporary array (one that's not displayed) and then use a max funtion on that, but there's got to be a more elegant way...

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
86Meesta2
  • 15
  • 3
  • I guess not. ... – Gustav Dec 11 '22 at 16:04
  • read this https://support.microsoft.com/en-us/office/create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm196631&ui=en-us&rs=en-us&ad=us – freeflow Dec 11 '22 at 16:41

1 Answers1

0

I guess I could write a loop and use LEFT/RIGHT to scrub all the values of their "ID-" and store those in a temporary array (one that's not displayed) and then use a max funtion on that, but there's got to be a more elegant way...

There is... Use an array formula.

=MAX(VALUE(SUBSTITUTE(A1:A13,"ID-","")))

Change the range as applicable. Instead of pressing Enter, press Ctl+Shift+Enter

enter image description here

For VBA you can use

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    
    Dim lRow As Long
    
    With ws
        '~~> Find the last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        Debug.Print Evaluate("=MAX(VALUE(SUBSTITUTE(A2:A" & lRow & ",""ID-"","""")))")
    End With
End Sub

Output

enter image description here

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Excellent! Thank you very much! Now more academic then anything, but just curious why lRow wouldn't be able to just be determined by .Rows.Count? aka why does .END(xlUp).Row need to come into play? – 86Meesta2 Dec 11 '22 at 17:47
  • Does [THIS](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba/11169920#11169920) answer your question? – Siddharth Rout Dec 11 '22 at 17:49
  • Ah, understood! In this case the data will always be contiguous but yes can see how issues would arise if it wasn't – 86Meesta2 Dec 11 '22 at 18:01
  • 1
    When data is not contigous then you can either find the last row using `.Find` as mentioned in the link or pass ranges like `Range("A1:A2,A4:A5,C3:C5,.....")` – Siddharth Rout Dec 11 '22 at 18:42