1

This is the code I have in a [very involved] spreadsheet someone made at work: 

Sub ClearSheet()
'
' Macro5 Macro
'
'
Range("E9,E2:F7,C14:I39,Q41:Q55,N14:N39,N41:N55").Select
Range("Q14").Activate
Range("E9,E2:F7,C14:I39,C41:I55,Q41:Q55,N14:N39,N41:N55,L41:L55").Select
Range("Q41").Activate
Selection.ClearContents

I have never so much as glanced at an excel macro before, so I had to look some things up. I get that the first range is selected and then Q14 becomes the active cell. Then that is done again, with some overlapping sections, and Q41 is made into the active cell. All to have the selections just be cleared out. I'm sure this is a simple question but I don't understand what the point is of the .Activates, or why someone would separate the sections that need to be cleared into two separate segments? From my very limited understanding, I thought Activate was something like focus, where that is now that cell that has focus for ease of use on the users side. But what good is that if the focus changes from the first cell to the second cell in a millisecond? All I know is that I need these cells:

E9,E2:F7,C14:I39,N14:N39,C41:I55,L41:L55,N41:N55,Q41:Q55

to clear out when this code is run, and if this code is doing something in addition to that, what is it? Is this just poorly written or am I too ignorant to understand? ~the novel~

L42
  • 19,427
  • 11
  • 44
  • 68
RachaelTheBlonde
  • 41
  • 7
  • there is no point in the `.Select` and `.Activate`, they are just auto generated using the [Macro Recorder](https://support.office.com/en-us/article/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b). – Slai Mar 09 '18 at 23:51

2 Answers2

1

Use 

Range("E9,E2:F7,C14:I39,N14:N39,C41:I55,L41:L55,N41:N55,Q41:Q55").ClearContents

Better still specify the workbook and worksheet to do this in e.g.

ThisWorkbook.Worksheets("Sheet1").Range("E9,E2:F7,C14:I39,N14:N39,C41:I55,L41:L55,N41:N55,Q41:Q55").ClearContents

Using sheet 1 as an example. You want to be sure to be in the right sheet before clearing stuff out. If you don't specify, and leave as just range, then the currently Active sheet is used.

In the code you talked about the each selection was shifting focus from the prior making the prior selections redundant.

Using Select, in particular, is not generally a good thing, it means 'touching' the sheet which incurs potentially unnecessary performance overhead.

As mentioned in comments, and indicated by ' Macro5 Macro, this is, at least in part, likely all, macro generated code. Macro meaning "many". Many instructions in this case. The macro recorder is verbose to say the least. It records everything your are doing including scrolling, mistakes in range selections etc. It is a good learning tool, and can often give useful insights into some objects and methods. The valuable skill is learning which elements to keep and how to turn this verbose code into structured programming.

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Yeah, what QHarr said. (That line replaces all 5 lines) Lines #1, 2 & 4 weren't really doing anything. :) – ashleedawg Mar 09 '18 at 23:55
  • Ok so basically, this was excel recording someones actions in the sheet? It is not code someone wrote? I'm not understanding this entire macro section I guess. There are a crap ton of formulas that are all over multiple pages of this workbook, and when I look through the macro code there are some that *appear* to be hard coded in. Like, they reset to a specified formula if I clear the sheet, as if someone had thought they would go away if it was cleared. But only cells that have no formula were set to clear... You gave me a perfect answer for my question though, I just have a lot more to learn – RachaelTheBlonde Mar 10 '18 at 01:19
0

The way you interpret Select and Activate is correct, one is for the actual selection and the other is somewhat to focus.

Select as the method name suggest selects the object. This method is not limited to Range Objects alone but is shared by most of the objects in Excel. Some of the examples:

Range("A1").Select '/* selecting a Range Object */
Worksheets("Sheet1").Select '/* selecting a Sheet Object */

Activate on the other hand works when you already selected an object.

Activates a single cell, which must be inside the current selection. To select a range of cells, use the Select method.

So what happens when you activate a cell not in the current selection?
It becomes the selected cell and as you've said, Excel executes the Select first and then the Activate in mili or nano or pico seconds (God knows how fast) interval.

In Range Objects the use of Select and Activate is almost interchangeable.
But you have to take note that there will be difference always with Selection and ActiveCell. For example:

Range("A1:B10").Select
Range("B5").Activate

Debug.Print Selection.Address
Debug.Print ActiveCell.Address

This means that you can actually do stuff (e.g. format, clear, add formula, add text etc.) on all cells you activate within the current selection but still preserves what Selection object points to.

There are cases that activating the object is vital. For example you want to select multiple worksheets like below and then select Range("A1") of Sheet3.

Worksheets(Array("Sheet1", "Sheet3", "Sheet5")).Select
Worksheets("Sheet3").Activate '/* vital */
Worksheets("Sheet3").Range("A1").Select

Above is the correct select command for multiple worksheet selection and selecting a range within 1 of the worksheets selected. But without the Activate part, there is a chance that it will return:

Run-time error '1004': Select method of Range class failed

because the first sheet in the array will always be the activated sheet object after the select. Now, how to avoid this troubles? Simple, avoid using select and activate. ~the novel sequel~

L42
  • 19,427
  • 11
  • 44
  • 68