0

I'm trying to declare all the ranges I'll be using in the rest of my code. It works fine when the range is in the same sheet as the button which calls the macro. When it is a simple range in an other sheet, it works as well but when I'm trying a range with end() it does not work in the other sheet.

Sheets("Current").Range("A1:B3") 'Ok
Sheets("Current").Range("A1", Range("B3").End(xlDown)) 'Ok
Sheets("Other").Range("A1:B3") 'Ok
Sheets("Other").Range("A1", Range("B3").End(xlDown)) 'NOk

Is there a reason the range().End() does not work ?

Thanks in advance.

rhommet
  • 13
  • 2
  • 2
    It's not best practise to use `xlDown` in the first place. [Check this post](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920) which should also have an answer to your issue. – Plutian Nov 25 '19 at 11:37
  • 3
    Your problem is that you don't full qualify the second range, you need to do that like the answer below. – Damian Nov 25 '19 at 11:38
  • @Plutian I want to use that range both to clear it and then to fill in a recordset later. So if my extract from yesterday was 10 records long and the one for today is 20, selecting only the used range would be 10 rows long when the range needs to be infinite. Am I missing something ? – rhommet Nov 25 '19 at 13:01

1 Answers1

3
Sheets("Other").Range("A1", Sheets("Other").Range("B3").End(xlDown))
Davesexcel
  • 6,896
  • 2
  • 27
  • 42