use of range and activate
 

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

--
Message posted from http://www.ExcelForum.com

use of range and activate
 

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

"crichardson " wrote in message
...
I have a spreadsheet that I am developing to add data extracted from a
database to various sheets. One procedure I have written clears old
results from the sheet - see below. The problem is more of an
annoyance than anything else but the vba code I have written means that
each sheet is displayed and, because I'm doing more than one sheet, the
screen goes crazy. I've tried turning of screen updating
(Application.ScreenUpdating = False) but this doesn't seem to have much
effect. What I am trying to achieve is to excute the vba code from
the first sheet as a kind of menu and to have the sheets updated behind
it in the background. It looks like it should be possible but I can't
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 With


---
Message posted from http://www.ExcelForum.com/

use of range and activate
 

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(xlToR ight))
set rng = wks.range(rng,rng.End(xlDown))
rng.ClearContents
next
end sub



--
Regards
Frank Kabel
Frankfurt, Germany

I have a spreadsheet that I am developing to add data extracted from
a
database to various sheets. One procedure I have written clears old
results from the sheet - see below. The problem is more of an
annoyance than anything else but the vba code I have written means
that each sheet is displayed and, because I'm doing more than one
sheet, the screen goes crazy. I've tried turning of screen updating
(Application.ScreenUpdating = False) but this doesn't seem to have
much effect. What I am trying to achieve is to excute the vba code
from the first sheet as a kind of menu and to have the sheets updated
behind it in the background. It looks like it should be possible but
I can't 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 With


---
Message posted from http://www.ExcelForum.com/

use of range and activate
 

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

Cliv

--
Message posted from http://www.ExcelForum.com

use of range and activate
 

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

Cliv

--
Message posted from http://www.ExcelForum.com