interresting phenomenon

WernerLouw

Board Regular
Joined
Dec 9, 2004
Messages
93
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... :unsure:
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Whisperer14

Well-known Member
Joined
Nov 6, 2002
Messages
589
A single liner would have also worked with suitable substitution.
Code:
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 (y)
 

WernerLouw

Board Regular
Joined
Dec 9, 2004
Messages
93
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top