Remove a number with a button

surfacesys · #1 (**permalink**) 01-11-05, 16:17

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

shades · #2 (**permalink**) 01-11-05, 17:30

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).

DavidCoutts · #3 (**permalink**) 01-12-05, 07:29

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

surfacesys · #4 (**permalink**) 01-12-05, 10:09

Thank You very much that worked great!

Michael

Internet Services

Web Development

Digital Marketing

Consumer Tech