Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: need a macro for a button

  1. #1

    Post need a macro for a button



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi
    Gurus

    the forum is awesome

    i need a macro to be assigned to a button
    the cells must be cleared but not clearing the formulas in the cell

    please give me the cod efor the macro to be assigned
    thanks

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    As you haven't stated which cells or range...etc this is the best i can do for you
    Code:
    Sub Clr_Cells()
    With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    If .HasFormula Then
    Else
    .ClearContents
    End If
    End With
    End Sub
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    hi please assume

    the range from f22:r33 has formulas in it and i need to clear only the cells from h22 to r 33
    i am not familiar withthe macro code
    so kindly let me know how can i clear the contents within this range which has formulas too

    thanks i appreciate your help

  4. #4
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    So you want to clear everything in your stated range including formula?
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  5. #5
    Quote Originally Posted by Simon Lloyd View Post
    So you want to clear everything in your stated range including formula?
    Simon may be i did not explain properly

    the data range starts from F22: R33
    and i need to clear the whole data range i.e from F22:R33 when ever i press the button1
    without clearing the formulas in it but only the contents

    so i have another button2 to bring data which is working perfectly fine


    thanks

  6. #6
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Try this:
    Code:
    Sub Clr_Cells()
    With ActiveSheet.Range("H22:R33").SpecialCells(xlCellTypeConstants)
    If .HasFormula Then
    Else
    .ClearContents
    End If
    End With
    End Sub
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  7. #7
    Quote Originally Posted by Simon Lloyd View Post
    Try this:
    Code:
    Sub Clr_Cells()
    With ActiveSheet.Range("H22:R33").SpecialCells(xlCellTypeConstants)
    If .HasFormula Then
    Else
    .ClearContents
    End If
    End With
    End Sub
    it asks me to debug pointing to this line
    With ActiveSheet.Range("H22:R33").SpecialCells(xlCellTypeConstants)

    i am sorry i am purely not into vb or programming stuff
    please advice

    thanks



  8. #8
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    one of two things, either the range you are clearing does not have any formula or as you have shown me the line as above it has a space that shouldn't be there between Ty and pe this should read type
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  9. #9
    Quote Originally Posted by Simon Lloyd View Post
    one of two things, either the range you are clearing does not have any formula or as you have shown me the line as above it has a space that shouldn't be there between Ty and pe this should read type
    Helo
    Simon

    Yes i have formulas and i have mistyped here but not inthe macro creation

    Please view my sample file and please advice on the same

    all ur helpis appreciated

    thanks
    Attached Files Attached Files

  10. #10
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    323
    Articles
    0
    Please supply a sample file with the code in place so i can see how you used it, there isn't any code in that workbook!
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Page 1 of 3 1 2 3 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
  •