use of range and activate

  • Thread starter Thread starter crichardson
  • Start date Start date
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
 
Run from any worksheet active:
Worksheets("Sheet2").Range("A1").CurrentRegion.ClearContents
(yes, all done in one line!)
Bob Umlas
Excel MVP
 
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
 
Back
Top