MAcro to clear all contents

wowkiter · May 15th, 2002, 11:57 AM

Hi all! I need a macro to clear the data fields in a workbook with 12 sheets of varying sizes (if that matters?).

Jack in the UK · May 15th, 2002, 12:16 PM

Sub Jack()
' Written by Jack in the UK 16th May 2002
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
' Add in sheets / name of all sheets upto 12
Sheets("Sheet1").Activate
'just makes something active so code executes OK
Selection.ClearContents
' Cleasr cells contence in one hit - gone
Sheet1.Select
Range("A1").Select
' just select any cell in sheet1 or whatever you name,
' I like to finish on a cell of some sort

End Sub

NateO · May 15th, 2002, 12:49 PM

What do you mean by data fields? Cells?

If it's just cells, you don't have to select anything using:



Sub clearly()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

   ws.UsedRange.ClearContents

Next ws

End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-15 11:55 ]

wowkiter · May 16th, 2002, 11:22 AM

Thanks! Will try both options
Tim

Joe Was · May 16th, 2002, 11:52 AM

Jack,
Its good to see commented code! If more people did it everyone would learn alot more.

Jack in the UK · May 16th, 2002, 12:18 PM

ermmm

i copy a few guys really im very poor at scripts, i have folowed a guy call joe was he seem to know a bit

Dave Hawley is my old pal, and my great friend Ivan F Moala these guys i like the style they use.. not taking a thing from the other GURUS, Mark suport Celtic so we call all ignor him even thou: hes MVP

I keep saying Celtic will never win the world cup, and Mark swares he wares a skirt!

Joe Was · May 16th, 2002, 12:29 PM

Me to Jack, Dave Hawley amazes me as does Ivan F Moala and I find Nate-O equally helpful. Aladdin as a sheet function man is the best I have ever seen. On the old board Dave helped me with some tough problems.

Mark O'Brien does good snippets and helps regularly. I should have remembered him right off, my wifes grandmother was an O'Brien. Juan writes killer code as well and knows some obscure tricks as well. So many people help out on this board that one could go on all day thanking the experts. JSW

[ This Message was edited by: Joe Was on 2002-05-16 11:43 ]

[ This Message was edited by: Joe Was on 2002-05-16 11:44 ]

May 15th, 2002, 12:16 PM	#2
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Sub Jack() ' Written by Jack in the UK 16th May 2002 Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select ' Add in sheets / name of all sheets upto 12 Sheets("Sheet1").Activate 'just makes something active so code executes OK Selection.ClearContents ' Cleasr cells contence in one hit - gone Sheet1.Select Range("A1").Select ' just select any cell in sheet1 or whatever you name, ' I like to finish on a cell of some sort End Sub __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

May 15th, 2002, 12:49 PM	#3
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	What do you mean by data fields? Cells? If it's just cells, you don't have to select anything using: Sub clearly() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.UsedRange.ClearContents Next ws End Sub _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-05-15 11:55 ]

May 16th, 2002, 12:18 PM	#6
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	ermmm i copy a few guys really im very poor at scripts, i have folowed a guy call joe was he seem to know a bit Dave Hawley is my old pal, and my great friend Ivan F Moala these guys i like the style they use.. not taking a thing from the other GURUS, Mark suport Celtic so we call all ignor him even thou: hes MVP I keep saying Celtic will never win the world cup, and Mark swares he wares a skirt! __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 15th, 2002, 11:57 AM	#1
wowkiter New Member Join Date: May 2002 Location: Tim Posts: 2	Hi all! I need a macro to clear the data fields in a workbook with 12 sheets of varying sizes (if that matters?).

May 16th, 2002, 11:22 AM	#4
wowkiter New Member Join Date: May 2002 Location: Tim Posts: 2	Thanks! Will try both options Tim

May 16th, 2002, 11:52 AM	#5
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	Jack, Its good to see commented code! If more people did it everyone would learn alot more.

May 16th, 2002, 12:29 PM	#7
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	Me to Jack, Dave Hawley amazes me as does Ivan F Moala and I find Nate-O equally helpful. Aladdin as a sheet function man is the best I have ever seen. On the old board Dave helped me with some tough problems. Mark O'Brien does good snippets and helps regularly. I should have remembered him right off, my wifes grandmother was an O'Brien. Juan writes killer code as well and knows some obscure tricks as well. So many people help out on this board that one could go on all day thanking the experts. JSW [ This Message was edited by: Joe Was on 2002-05-16 11:43 ] [ This Message was edited by: Joe Was on 2002-05-16 11:44 ]