interresting phenomenon

[WernerLouw]

I have this interrresting phenomenon...

Wrote the following code to clear comments from a list of sheets.

Sub ClearComments()

ThisWorkbook.Activate
Sheet1.Select
Range("g7:m42").ClearContents

Sheet2.Select
Range("g7:l33").ClearContents

Sheet3.Select
Range("g7:m31").ClearContents

'etc etc

End Sub

but it would not run past sheet 2, an error occured that said that merged cells need to b eidenitically sized. But the cells on all the sheets are merged. Then i changed the code in the following manner...

Sub ClearComments()

ThisWorkbook.Activate
Sheet1.Select
Range("g7:m42").ClearContents

Sheet2.Select
Range("g7:l33").Select
Selection.ClearContents

Sheet3.Select
Range("g7:m31").ClearContents

'etc etc

End Sub

Now it works without a glitch. What changed? all I am doing is I am selecting the range before clearing the contents...

 

[Whisperer14]

A single liner would have also worked with suitable substitution.

Sheets("Sheet1").[A1:B3].ClearContents

Hopefully one of the big guns will pick this up and clarify what I am about to say.
I have been told off for the way that I used 'Activate' and 'Select' in the past. Now I 'Select' a Range of cells only, I know that 'Select' works the way you have used it but I would now use 'Activate' for Sheets. THe only time that I use 'Activate' on cells is when I want a particular cell to be the Activecell.

HTH

 

[WernerLouw]

I understnad the logic behind using one liners, but... sometimes they just dont want to work... Me and a colleague have been struggling quite a few times with code that does not want to work and when you split the code into selecting the first part, then the second part and doing the action on that it works...

Dont know why... it works.... just thought it would be interresting to know the answer...