0

I'm struggling with the use of a string constant in an Excel formula.

I'm trying to get Last Row of a Excel range using Match Function.

Suppose we have the code below:

Const Range1 As String = "A1"

LastRow = sht.Range("A1").CurrentRegion.Rows.Count      'Works Ok
LastRow = sht.Range(Range1).CurrentRegion.Rows.Count    'Works Ok Too.

'The problem is with Match...
Const Range1 As String = "A:A"
LastRow = sht.[match(2,1/(A:A<>""))]        'Works Ok

LastRow = sht.[match(2,1/(Range1<>""))]        'Doesn't Work.

I think that method is the most efficient because it ignores empty cells and formulas without result (="").

Community
  • 1
  • 1
  • Reformat your question, everything is jumbled around. – Ashwin Dec 16 '19 at 10:39
  • Try LastRow = sht.[match(2,1/(Range(Range1)<>""))] but the constant should be defined as "A:A" too. Here are multiple examples of how to find the last row https://stackoverflow.com/questions/40650508/excel-vba-find-last-row-in-range?r=SearchResults – Ricardo Diaz Dec 16 '19 at 10:43
  • I think the best way to do is, first get the last cell in the column by using `.End` , make this as `active cell` then again use `End(xlToRight)` to select that row from the active cell. – Ashwin Dec 16 '19 at 10:46

1 Answers1

1

.[] evaluates a given string, but cannot cope with variables. You have to use .Evaluate(..your concatenated string...) if you plan on using variables:

LastRow = sht.Evaluate("MATCH(2,1/(" & Range1 & "<>""""))")

That being said, not sure why you try to return a LastRow variable this way. Have a look at this post on SO for possible better ways.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Hi JvdV. I'm pleased. That's a great answer. As mentioned above, probably after your answer, I think that method is the most efficient, for find the very last cell with data, because ignores empty cells and formulas without result (=""). –  Dec 16 '19 at 11:11