use of range and activate

C

crichardson

I have a spreadsheet that I am developing to add data extracted from
database to various sheets. One procedure I have written clears ol
results from the sheet - see below. The problem is more of a
annoyance than anything else but the vba code I have written means tha
each sheet is displayed and, because I'm doing more than one sheet, th
screen goes crazy. I've tried turning of screen updatin
(Application.ScreenUpdating = False) but this doesn't seem to have muc
effect. What I am trying to achieve is to excute the vba code fro
the first sheet as a kind of menu and to have the sheets updated behin
it in the background. It looks like it should be possible but I can'
find the code to make it happen. Any suggestions?

Clive

With Worksheets("sheet2")
.Activate
With ActiveSheet
.Range("A1").Activate
.Range("A1", Selection.End(xlToRight)).Select
.Range(Selection, Selection.End(xlDown)).ClearContents
End With
End Wit
 
B

Bob Umlas

Run from any worksheet active:
Worksheets("Sheet2").Range("A1").CurrentRegion.ClearContents
(yes, all done in one line!)
Bob Umlas
Excel MVP
 
F

Frank Kabel

Hi clive
try
sub foo()
dim wks as worksheet
dim rng as range

For each wks in activeworkbook.worksheets
set rng = wks.Range(wks.Cells(1,1),wks.Range("A1").End(xlToRight))
set rng = wks.range(rng,rng.End(xlDown))
rng.ClearContents
next
end sub
 
C

crichardson

Ah... I can tell you guys do a lot more of this stuff than I do!
Thanks.

Cliv
 
C

crichardson

Ah... I can tell you guys do a lot more of this stuff than I do!
Thanks.

Cliv
 
Top