2

In Excel VBA, to create a group of rows I have seen many articles which use Range.Select followed by Selection.Group. However, that convulses the UI, as the process of setting Selection actually sets Excel's visible selection. (I guess Selection is actually Application.Selection?)

There must surely be a way to set a group without using Selection? Is there perhaps a sheet groups property to which one can add rows? Or perhaps the real question is how to create a Selection without using the visible Selection?

Zoe
  • 27,060
  • 21
  • 118
  • 148
gwideman
  • 2,705
  • 1
  • 24
  • 43
  • 5
    What do you mean? To Group the rows in range `A1:A3` for instance you can just do `Range("A1:A3").Rows.Group`. – BruceWayne Feb 09 '17 at 23:34
  • 4
    Almost always (there are always exceptions) if you see something like `x.Select` followed by `Selection.y`, it can be combined as `x.y`. – YowE3K Feb 10 '17 at 00:16
  • @BruceWayne Thanks for the prod, I'll give this a try. So why do so many existing articles use Selection? Hmmm. – gwideman Feb 10 '17 at 00:50
  • 2
    @gwideman that's a good question, because generally it's best practice to [avoid using `.Select`/`.Activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). A guess is because if you use the macro recorder to record some VBA, it's riddled with the use – BruceWayne Feb 10 '17 at 00:58
  • @BruceWayne and YowE3K: Well no surprise to you guys I'm sure, but Range("whatever").Rows.Group works great. By great, I mean it does the job, and it's soooooo much better behavior than using Selection. Bruce -- feel free to write up an answer and I'll give it the check mark. – gwideman Feb 10 '17 at 05:35
  • 2
    @BruceWayne My theory on the wide usage of `Selection` is that many vba users learn by recording macros, and the UI for excel pretty exclusively relies on users selecting a cell before doing anything to it. – CodeJockey Mar 03 '17 at 18:07

1 Answers1

0

Just replace selection.rows.group with Range(yourRange).rows.group
where yourRange is either replaced with a literal range or used as a range variable.

Unless you always only work with one excel document at any given moment:
A good practice is to always use ThisWorkbook.sheets(sheet1.name).Range(yourRange):
This applies the code in the same workbook as the code reside in.

If you don't, VBA will default to ActiveWorkbook.ActiveSheet.Range(yourRange)

user 88 91
  • 65
  • 7