0

I want to make range in Excel VBA from the following:

  1. Find the cell with a phrase (this is expected to be unique per sheet)
  2. Make that entire row of used cells a Range object so I can use the Find command on it

1 is easy and done. Let's say it is cell $A$34. For 2 I want the last cell in row 34 with a value, say, cell $RS$34.

Then I can set variable = Range($B$34:$RS$34) and use range commands on it.

I can use the UsedRange.Column property to get the numeric equivalent of "RS" but is there a an inbuilt way to get the "RS"?

Edit:

Found solution in a not accepted answer here Function to convert column number to letter?

Use .Cells(Row, Column).Address to return a classic Excel cell reference. Forgot to think recursive enough when applying Excel commands.

user24007
  • 121
  • 9
  • Does this answer your question? [Function to convert column number to letter?](https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter) – braX Nov 25 '22 at 04:36
  • @braX Thanks, yes, but wondering if there is a built in I do not know about. – user24007 Nov 25 '22 at 05:06
  • @braX One of the "not answers" is what I was after. .Cells(Row,Column).Address will give me the "proper" cell reference. – user24007 Nov 25 '22 at 05:10
  • Yes, that topic gives you lots of options - what works best depends on the full solution. – braX Nov 25 '22 at 06:14
  • 1
    Why do you need the Letter representation? Seems simple to just use the column number in the `Cells` property to set the range. – Ron Rosenfeld Nov 25 '22 at 13:02
  • @Ron Rosenfeld You are right. I did not try that until just now. Set Rng = ws.Range(ws.Cells(1,1), ws.Cells(1,10)) does the job too. – user24007 Nov 28 '22 at 01:52

0 Answers0