Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clear Contents (Xls 2003)

    Hi all,
    I'd like to clear the contents in the range except the headers in macro
    How can I delete or clear just the values and not the first row's headers?
    LISTING 1:

    HeaderA HeaderB HeaderC
    20
    5 150 75
    50


    LISTING 2:

    HeaderA HeaderB HeaderC
    7 15 25
    35


    TIA
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear Contents (Xls 2003)

    Can you explain what the purpose of Listing 1 and Listing 2 is, and if there is any relation between them?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Contents (Xls 2003)

    Hi Hans

    there are no relationship between them,


    thanks
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear Contents (Xls 2003)

    So what exactly do you want?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Clear Contents (Xls 2003)

    What are the ranges you want to clear the contents of?

    You can use something like:
    Range("A2:C100").clearcontents

    If A1:C1 has the headers....

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Contents (Xls 2003)

    Hi Steve,

    this clear everything including the 2nd listing. I may not have explained myself very clearly.
    I am trying to show an example that there are 2 list of data, namely Listing 1 and Listing 2 in the same
    sheet and both these list have no relationship.

    I want to clear the contents in Listing 1 and Listing 2 without removing their headers.

    thanks
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Contents (Xls 2003)

    How would the macro know what rows the headers are in?
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Contents (Xls 2003)

    that is precisely what I want to accomplish and whether it is doable with 2 or more headings in a sheet.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear Contents (Xls 2003)

    You haven't answered Legare's question yet: how can the macro recognize the header rows?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Clear Contents (Xls 2003)

    Are teh ranges named "Listing1" and "Listing2" and do you want to clear all but the first row? If so you can use something like:

    <pre>Dim x As Integer
    For x = 1 To 2
    With Range("Listing" & x)
    .Resize(.Rows.Count - 1, .Columns.Count). _
    Offset(1, 0).ClearContents
    End With
    Next</pre>


    Alternately if the headers are text and everything else are number constants you could use:

    <pre>Dim x As Integer
    For x = 1 To 2
    With Range("Listing" & x)
    .SpecialCells(xlCellTypeConstants, _
    xlNumbers).ClearContents
    End With
    Next</pre>


    If you are after something else can you elaborate?

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Contents (Xls 2003)

    Hi Hans

    Apology that I can't express very well on what I want to accomplish. I have attached a sample. In the sample,
    I want to clear the contents in those cells that were highlighted leaving the headers intact.


    thanks

    btw, I was trying to attach a crop screenshot of the xls sheet to this post as image but was unable. any idea on the software that I should use
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear Contents (Xls 2003)

    The following macro will clear all cells with constants except text values:

    Sub ClearAllExceptHeaders()
    Dim rCell As Range
    For Each rCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
    If Application.WorksheetFunction.IsNonText(rCell) Then
    rCell.ClearContents
    End If
    Next rCell
    End Sub

    You can create a screenshot like this:
    - Press PrintScreen to copy a picture of the entire screen to the clipboard, or
    - Press Alt+PrintScreen to copy a picture of the foreground window to the clipboard.
    - Select Start | All Programs | Accessories | Paint.
    - Select Edit | Paste or press Ctrl+V.
    - Use the rectangle tool to select the part you want (no more than 640 x 480 pixels).
    - Move it to the upper left corner.
    - Click outside the rectangle.
    - Drag the right edge of the picture to the left using the little black square in the middle.
    - Drag the bottom edge upward.
    - Save as a .png file.

    There are also many utilities for creating screenshots, some of them free.

  13. #13
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Contents (Xls 2003)

    Hi Hans,

    Thanks, this work well. I have a couple of questions for my understanding :

    Instead of using a For Each ....Next loop, is there a faster way than this
    What is the differences between ActiveSheet.UsedRange and ActiveSheet.CurrentRegion

    thanks
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  14. #14
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Contents (Xls 2003)

    Hi Hans,

    Thank for the explanation. Just to clarify it should be UsedRange instead of UsedRegion and I should use ActiveCell.CurrentRegion for
    non blank rows and columns

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear Contents (Xls 2003)

    Edited by HansV to correct mistake (I originally wrote UsedRegion instead of UsedRange in one place)

    If the cells you want to clear always contain numbers, you can omit the loop and use this:

    Sub ClearAllExceptHeaders()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants, _
    xlNumbers).ClearContents
    End Sub

    UsedRange is the smallest rectangular range that contains all non-blank cells in a worksheet.

    CurrentRegion is not a property of a worksheet, but of a cell or group of cells. It is the smallest rectangle around the cell or cells that doesn't contain a blank row or blank column.
    You can't use ActiveSheet.CurrentRegion.

    In the picture below, the UsedRange of the sheet is indicated with a red border, and the CurrentRegion of cell B8 is surrounded by a blue rectangle.
    Attached Images Attached Images
    • File Type: png x.PNG (7.0 KB, 0 views)

Page 1 of 2 12 LastLast

Posting Permissions

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