Results 1 to 10 of 10

vba clear contents

This is a discussion on vba clear contents within the Excel Questions forums, part of the Question Forums category; Hi Gurus (searched other posts but none made sense to me) I dont know how to copy an excel sheet ...

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    18

    Default vba clear contents

    Hi Gurus

    (searched other posts but none made sense to me)

    I dont know how to copy an excel sheet into the question.

    But assuming the following text starts at CELL C2 (this is just a section of the workbook)

    C D E F G

    1 x 5 5 Correct!
    2 x 5 5 Try Again?
    3 x 5 5 Try Again?
    4 x 5 5 Try Again?
    5 x 5 5 Try Again?


    This is a worksheet to test multiplication tables. I am trying to create a macro button to clear the contents of column F, for the kids to start again?

    Please assist ? THANK YOU in advance!
    Last edited by hoffies; Apr 8th, 2009 at 03:02 AM. Reason: Content change

  2. #2
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    553

    Default Re: vba clear contents

    The only bit that's even marginally difficult is telling the macro which bits to clear contents in.

    A named range works well, or we can write a macro so that it works down the entire list, and then you can add extra questions below the current ones without needing to change anything else.

    Which would you prefer?
    "I'm almost sure I'm not mad..." - Stoppard

  3. #3
    New Member
    Join Date
    Apr 2009
    Posts
    18

    Default Re: vba clear contents

    I could name the range in column F if that would make it easier


    For instance ANSWERS

  4. #4
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default Re: vba clear contents

    Without the named range and assuming that there will always be an entry in column C:

    Code:
    Sub Clear_Stuff()
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, 3).End(xlUp).Row
    Range("F2:F" & lastrow).ClearContents
    End Sub
    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    18

    Default Re: vba clear contents

    It works like a charm.


    THANKS Domski!!

  6. #6

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: vba clear contents

    Isn't
    Code:
    With ActiveSheet
        .Columns("f").ClearContents
    End With
    or
    Code:
    With ActiveSheet
        Intersect(.UsedRange, .Columns("f")).ClearContents
    End With
    good enough ?

  7. #7
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default Re: vba clear contents

    Quote Originally Posted by Seiya View Post
    Isn't
    Code:
    With ActiveSheet
        .Columns("f").ClearContents
    End With
    or
    Code:
    With ActiveSheet
        Intersect(.UsedRange, .Columns("f")).ClearContents
    End With
    good enough ?
    Maybe but the OP stated that it needs to start in F2 so I assume that there is a heading in Row 1.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

  8. #8
    New Member
    Join Date
    Apr 2009
    Posts
    18

    Default Re: vba clear contents

    Yes, Thats correct Domski - Row 1 is in use. Dont want to clear that row, just the stuff below.

    Basically, I named the range that I wanted cleared in column F and it worked fine with your code.

    Thanks

  9. #9

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: vba clear contents

    But assuming the following text starts at CELL C2 (this is just a section of the workbook)
    Yeah, I missed this...

  10. #10
    Board Regular Domski's Avatar
    Join Date
    Jan 2005
    Location
    Leeds, UK
    Posts
    7,178

    Default Re: vba clear contents

    Quote Originally Posted by hoffies View Post
    Basically, I named the range that I wanted cleared in column F and it worked fine with your code.
    No need to name the range.

    This might be better as well as the original code was dependent on there being an entry in column C which is not necessary either on reflection:

    Code:
    Sub Clear_Stuff()
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, 6).End(xlUp).Row
    Range("F2:F" & lastrow).ClearContents
    End Sub
    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    - Guidelines For Posting

    - Mr Excel Articles and PodCasts

    - Display sheet using HTML Maker or Excel Jeanie

    - Something that makes me laugh

    - Please try to remember to use code tags when posting your VBA code: [code] Your code here [/code]

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