Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Excel VBA to Clear Ranges

    A spreadsheet contains 31 daily ranges of 24 hourly counts (two rows per hour). I've protected all but the 31 ranges of 24x2.

    At the end of each month I would like to clear each of these 31 ranges to set up the next month's worksheet for data entry. Is there a shortcut to clear the ranges using GoTo or Find? Look's like Protection will first need to be disabled.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I'm attaching a copy of the spreadsheet. If I unlock the protection I can use GoTo Special, but whether I select Constants or Formulas Excel highlights formulas and other cells that I want to preserve.

    I could try to record a macro to clear the data-entry ranges, but am hoping to learn of a shorter method.
    Attached Files Attached Files
    Last edited by Arcturus16a; 2012-10-11 at 19:17.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    It may be easiest, as a first step, to give the range of cells you want to clear a name (let's say DataEntry). The cells in the range don't have to be contiguous.

    Then you can include code like this in a Macro.

    Code:
    For Each Item in Worksheets("Whatever the Worksheet's name is").Range("DataEntry").Cells
        Item.Value = 0
    Next
    The Macro will write a zero in each cell in the named range. Or you could write a blank in each cell . . . Item.Value = ""

    You can assign the Macro to a button, or give it a shortcut key if you like.

    You could either clear all the cells at once by having them in a single range as above, or give each of your ranges a separate name and clear each in turn.

    Hope this is sufficient to get you going - post back if you need more information.
    Last edited by MartinM; 2012-10-11 at 19:19.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Martin. After I gave the GoTo shortcut a try and it failed me I suspected that I would need to create named ranges for each of the 31 days and then use them in the macro.

    Or maybe I should just stick with cell references since I'll be repeating the worksheet 12 times in a workbook. I don't want the range names bumping into one another (that would make 372 ranges, wouldn't it?).

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Whatever suits you best.

    If the location of all those cells doesn't change then I'd be tempted to make one large non-contiguous range which includes them all !

  6. #6
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    It's pretty easy to give all your data entry cells a name.
    - Hold down the control button and highlight each section that you want to be able to clear.
    - Near the top left of the page you'll see a cell on the formula line that shows a cell number, called the Name Box. (Name Box should pop up when you highlight it)
    - With the cells selected, type the name you want to give the range there. Note the name should be text, no numbers, some other characters may be allowed such as underscore.
    - Click on another cell to deselect your range, then click the drop down in the Name Box and select your range name. This should highlight all you want to delete, then simply press delete.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 649 Times in 592 Posts

    Using VB

    Here is a very simple code that if attached to a button placed on the sheet will erease your entry (unprotected) cells:

    Private Sub CommandButton1_Click()
    For I = 11 To 101 Step 3 'Skip 3 rows
    Range(Cells(I, 5), Cells(I, 28)).Value = 0 'Erase Carded row
    Range(Cells(I + 1, 5), Cells(I + 1, 28)).Value = 0 'Erase Uncarded row
    Next I
    End Sub



    HTH,
    Maud

Posting Permissions

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