0

If I have continuous data in cells A6:G15, the below lines of code select/highlight that data similar to pressing "ctrl + shift + down + right". And they all work.

For reference, Line 2 and 3 depend on Line 1 but Line 4 stands on its own.

My question is what are the back-end differences and which may be better in certain situations?

'Line 1
Sheets("Sheets1").Range("A6").Activate

'Line 2
Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select

'Line 3
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select

'Line 4
Range("A6", Range("A6").End(xlDown).End(xlToRight)).Select
arks
  • 55
  • 8
  • 4
    This is tangential to your question but don't use `.select`. – findwindow Apr 08 '22 at 20:55
  • 3
    Also, if you're trying to find the last cell on the sheet, see [this canonical](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Apr 08 '22 at 20:58
  • `activesheet.usedrange.select` is selecting everything too. – MaMe Apr 08 '22 at 21:00
  • Might A6:G15 have any empty cells in the first or last rows? That could make a difference in what ends up getting selected. – Tim Williams Apr 08 '22 at 21:35
  • I didn't intend for them to have empty cells. Do either of those work differently if there are sporadic empty cells? – arks Apr 25 '22 at 22:22

1 Answers1

1

First deviation is the target worksheet:

Line 4 is not the same as 1+2 or 1+3, since it is lacking of the worksheet. Line 4 has no worksheet definition, so it refer to the active worksheet.

Second deviation is the event firing of Worksheet_SelectionChanged:

Line 4 is firing the Worksheet_SelectionChanged Event only one time. Line 1+2 or 1+3 are firing the event two times. One time for using the activate() function of line 1 and a second time by using the select() function of line 2 or 3. Using the End() function will not fire any event.

Try to avoid as many selection/activation function calls as possible when writing a macro.Thats because you never know if the user is deselecting something what your macro has selected by itself during its runtime.

If you append the target worksheet definition into Line 4, you can be sure that the function call will behave the same all the time:

Range(sheets("Sheets1").Range("A6"), sheets("Sheets1").Range("A6").End(xlDown).End(xlToRight)).Select

MaMe
  • 235
  • 1
  • 7