Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Unanswered: Remove a number with a button

    I have a list of numbers on a spreadsheet ie:
    ....A... B... C... D... E
    1 500 501 502 503 504
    2 505 506 507 508 509
    3 510 511 512 513 514

    I would like to enter a number in a cell and then hit a button to eliminate that number from my list. Is there code I can write to do this??

    ie: enter 506 then hit a button and it would remove 506 from the list example

    ....A....B....C....D....E
    1 500 501 502 503 504
    2 505...... 507 508 509
    3 510 511 512 513 514

    I have given the list of numbers a defined name as "Numbers"

    Anyone that could help, I would greatly appreciate it! Or refer me to a website that may help!

    Thank You,
    Michael
    Gotta to do some code

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    This code will act upon whatever is in cell A1. Just a note: it is very bad practice to name ranges with something that Excel uses (i.e. "Numbers"). Thus, in this code, the named range is changed to myNum. Adjust the ranges to suit your needs.

    Code:
    Sub test()
        Dim myNum As Range
        Dim c As Range
        Dim Crit As Range
        
        Set Crit = Range("A1")
        Set myNum = Range("B3:E6")
        For Each c In myNum
            If c.Value = Crit.Value Then
                c.ClearContents
            Else
            End If
        Next c
    End Sub
    After you check this out, you can go to View > Toolbars > Forms, and choose CommandButton, and draw a button on the sheet, when you release the mouse button it will pop up a list of macros to assign, choose this one (or if you change the name, use that new name).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Just another quick option

    Code:
    Sub Test()
        Dim rngVal As Range
        Dim rngEmpty As Range
        
        
        Set rngVal = Range("A1")
        Set rngEmpty = Range("B3:E6")
        rngEmpty.Replace rngVal.Value, "", xlWhole
        
    End Sub
    And then attach it to a button (either as above or via control toolbox and call the sub from the Buttons Click Event)

    All the Best
    Dave

  4. #4
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Thank You very much that worked great!

    Michael
    Gotta to do some code

Posting Permissions

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