Results 1 to 7 of 7

MAcro to clear all contents

This is a discussion on MAcro to clear all contents within the Excel Questions forums, part of the Question Forums category; Hi all! I need a macro to clear the data fields in a workbook with 12 sheets of varying sizes ...

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Tim
    Posts
    2

    Default

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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,129

    Default

    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"

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    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 ]

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Tim
    Posts
    2

    Default

    Thanks! Will try both options
    Tim

  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,129

    Default

    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"

  7. #7
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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 ]

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com