title: VBA get the max number based on condition
body: <p>I have some data in Excel where I have to find the max number in a column so that I can increment it by one. The the numbers are as text like 001. There are also three reference columns I have to consider to identify the max number.</p>
  
  <p>Here is an example of the data</p>
  
  <pre><code>A    K    L    N   &lt;-These are the columns 
  Ref1 Ref2 Ref3 ID  &lt;-These are the headers
  xxx  I    17   001 &lt;-Column N is also text 
  xxx  I    17   002
  yyy  J    15   001
  xxx  I    17   005 &lt;-The numbers might not be consecutive. Instead of 003 jumps to 005
  </code></pre>
  
  <p>Based on the reference XXX-I-17 I have to find that the next ID is 006. Note that the reference is not always consecutive as indicated above.</p>
  
  <p>What I'm doing is first to filter the data like:</p>
  
  <pre><code>LastRow = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
  ActiveSheet.Range("A1:L" &amp; lastRow).AutoFilter Field:=1, Criteria1:="xxx"
  ActiveSheet.Range("A1:L" &amp; lastRow).AutoFilter Field:=11, Criteria1:="I"
  ActiveSheet.Range("A1:L" &amp; lastRow).AutoFilter Field:=11, Criteria1:="17"
  </code></pre>
  
  <p>I'm not sure if filtering the data is the best approach, but what can not figure out is how to find the max value in column N so that I can increment it.</p>
  
  <p>Any ideas? Would it work better with formulas?</p>