Clearing Cells in Multiple Worksheet

E

EAGER

Hi

Did anyone know how to create a macro to clear particular cell range in multiple files
Ex: I have 3 files which inside contains a list of friends email address and telp no. I need to clear a range of cell, let's say B5:F100 of all the three file at the same time. Can I do this

Thks for someone to reply this.
 
F

Frank Kabel

Hi
try the following:
- select the range on your first sheet
- hold down sthe SHIFT key and select all other sheets
- hit DEL
-----Original Message-----
Hi,

Did anyone know how to create a macro to clear particular cell range in multiple files?
Ex: I have 3 files which inside contains a list of
Click to expand...
friends email address and telp no. I need to clear a range
of cell, let's say B5:F100 of all the three file at the
same time. Can I do this?
 
F

Frank Kabel

Hi
sorry, I misunderstood your question. Are you talking
about three separate Excel files?. If yes, try something
like the following (note: all files have to be opened, the
macro clears the range always on 'sheet1')

sub foo()
dim wbk as workbook
dim wks as worksheet
for each wbk in workbooks
set wks=wbk.worksheets("Sheet1")
wks.range("B1:G10").clearcontents
next
end sub
 
L

Lady Layla

HAve you tried recording, deleting, end macro and reading the code?


: Thnks. But I dont need that. I need a macro to do it.
:
: Eager
 
E

EAGER

YEs, thanks for the macro. But I have that macro actually,which is not up to the requirement
Give you another example...

I have 3 excel files which had 5 worksheet on each file. At each worksheet I have data from range B10:T100
So I need a macro that can clear >all that range< in every worksheet of 3 files at the same time
Please advise.........

Thank for the expertise.................
 
F

Frank Kabel

Hi
the use the following:
sub foo()
dim wbk as workbook
dim wks as worksheet
Dim rng as range
for each wbk in workbooks
for each wks in wbk.worksheets
set wks=wbk.worksheets("Sheet1")
set rng = wks.range("B10:T1000")
rng.clearcontents
next
next
end sub
 
Top