0

I have a range of data that will be between columns B and D inclusive.

I can get the top left most range as this will be constant, the only thing that varies is the bottom right cell.

I tried obtaining the bottom right cell via using xltoright and then xltobottom. However I am having difficulties obtaining the cell address of this bottom right cell.

Has anyone come across this problem and if so how did you over come the issue?

SeekingAlpha
  • 7,489
  • 12
  • 35
  • 44
  • 1
    Simple :) Use `.Find` :) See [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) – Siddharth Rout Dec 19 '13 at 05:09
  • 2
    you can find a lot of answet to that question here. [This](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) post is one example. – L42 Dec 19 '13 at 05:12
  • @sid lol, i reference the OP to the same link :P – L42 Dec 19 '13 at 05:16
  • So many questions. [an earlier one](http://stackoverflow.com/q/4872512/641067) – brettdj Dec 19 '13 at 09:19
  • `CurrentRegion` could work e.g. `Set rng = Range('B1').CurrentRegion` – Alex P Dec 20 '13 at 12:03
  • Are you able to get to the bottom right cell? You can use cell.address to get that address. So, if your top left cell is A1, you can try: `Dim lastCell lastCell = Cells(1,1).End(xltoRight).End(xlDown).Address` – BruceWayne Jun 17 '15 at 18:31

1 Answers1

0

change xltoright and then xltobottom, to : xltoleft and then xlUp. Check the answers you get from it in the immédiate window of VB editor (Ctrl+G)

if still doesn't work maybe use:

Range(Range_Adddress).cells.(Range(RangeAddress).cells.count).address

to get to the last cell's address of your range (I named it Range_Address, which has to be a string in the example).

Does this help you?

ChrisM
  • 1,576
  • 6
  • 18
  • 29
Patrick Lepelletier
  • 1,596
  • 2
  • 17
  • 24