Solved: Deleting numbers automatically

[sjoyce115]

Hello:

The codes listed below allows me to enter a number into an input box, and the number is deleted if found within the worksheet?s range. I have to find and delete several hundred numbers, and it is becoming somewhat cumbersome to do this manually. Can someone help me improve the codes to allow me to copy and paste several numbers at once into the input box? Your help or comments would be very much appreciated.




Sub Delete_Check()
Dim rngI As Range
Dim strChk As String

start:
strChk = InputBox("Which Check?", "Check Deleter")
If strChk = vbNullString Then
GoTo theEnd
Else
With Worksheets("Sheet1").Range("A:A")
Set rngI = .Find(strChk)
If rngI Is Nothing Then
MsgBox "Check " & strChk & " not found"
GoTo start
Else
rngI.EntireRow.Delete
MsgBox "Check " & strChk & " deleted"
End If
End With
GoTo start
End If
theEnd:

End Sub

[MWE]

Hello:

The codes listed below allows me to enter a number into an input box, and the number is deleted if found within the worksheet?s range. I have to find and delete several hundred numbers, and it is becoming somewhat cumbersome to do this manually. Can someone help me improve the codes to allow me to copy and paste several numbers at once into the input box? Your help or comments would be very much appreciated.
There are several ways that this could be improved:
1a. manually entering multiple numbers (rather than a single number)
1b. your copy and paste approach (quite similar to 1a)
2. having the procedure look at a specific place in the current sheet or some other sheet for numbers
3. setting up a UserForm to enter multiple numbers

Each method has advantages and disadvantages.

Which option would you prefer?

[sjoyce115]

There are several ways that this could be improved:
1a. manually entering multiple numbers (rather than a single number)
1b. your copy and paste approach (quite similar to 1a)
2. having the procedure look at a specific place in the current sheet or some other sheet for numbers
3. setting up a UserForm to enter multiple numbers

Each method has advantages and disadvantages.

Which option would you prefer?


Hi

I like option 2; having the procedure look at another sheet(say sheet#2) for numbers.

[bodhi]

I like option 2; having the procedure look at another sheet(say sheet#2) for numbers.

Try this and see if it what you are after:
First create a Command Button on sheet 2 in the code below i have called mine btnDelete. Paste this code into the code space for sheet 2.

Private Sub btnDelete_Click()
Dim num As Integer
Dim finishRow As Long

Worksheets("Sheet2").Range("A1").Select
Selection.End(xlDown).Select ' select consecutive data in column A
finishRow = ActiveCell.Row ' row with last data
num = finishRow

Delete_Check (num)
End Sub

then paste this into a module:

Sub Delete_Check(num As Integer)
Dim rngI As Range
Dim strChk As String
Dim delCount As Integer
Dim i As Integer
count = 0

start:
For i = 1 To num
strChk = Worksheets("Sheet2").Range("A" & i).Value

If strChk = vbNullString Then
GoTo theEnd
Else
With Worksheets("Sheet1").Range("A:A")
Set rngI = .Find(strChk, LookAt:=xlWhole, MatchCase:=True)
If rngI Is Nothing Then
GoTo theEnd
Else
delCount = delCount + 1
Worksheets("Sheet3").Range("A" & delCount).Value = strChk
rngI.EntireRow.Delete
End If
End With
GoTo start
End If

theEnd:
Next i

End Sub

it is a bit crude but i am only new to all of this. Maybe others who have more experience could comment?

Hope this helps.
Brent.http://vbaexpress.com/forum/images/smilies/023.gif

[sjoyce115]

Hi Bodhi


Try this and see if it what you are after:
First create a Command Button on sheet 2 in the code below i have called mine btnDelete. Paste this code into the code space for sheet 2.



Thanks for your response. I don't know how to create a command button. Can you please tell me how?

[gibbo1715]

You can create a command button as follows

1. on the menu in excel click view - toolbars - control toolbox

then a menu will appear with some icons on, one is of a button, click this and then click somewhere near the top of sheet 2.

2. double click your new button you will be taken to the vbe and will see something similar to

Private Sub CommandButton1_Click()
End Sub

Now paste the following in between Private Sub CommandButton1_Click() and the end sub

Dim num As Integer
Dim finishRow As Long

Worksheets("Sheet2").Range("A1").Select
Selection.End(xlDown).Select ' select consecutive data in column A
finishRow = ActiveCell.Row ' row with last data
num = finishRow

Delete_Check (num)


Next you need to create a module as follows, on the menu (Still in the VBE) at the top click insert - module, your right hand pane will become blank, copy the code below into it



Sub Delete_Check(num As Integer)
Dim rngI As Range
Dim strChk As String
Dim delCount As Integer
Dim i As Integer
count = 0

start:
For i = 1 To num
strChk = Worksheets("Sheet2").Range("A" & i).Value

If strChk = vbNullString Then
Goto theEnd
Else
With Worksheets("Sheet1").Range("A:A")
Set rngI = .Find(strChk, LookAt:=xlWhole, MatchCase:=True)
If rngI Is Nothing Then
Goto theEnd
Else
delCount = delCount + 1
Worksheets("Sheet3").Range("A" & delCount).Value = strChk
rngI.EntireRow.Delete
End If
End With
Goto start
End If

theEnd:
Next i

End Sub


Now return to excel and on your control toolbox menu click on the top left icon (Looks like a triangle with a ruler below it)

Now click your button

Would post an example but dont have winzip at my work so hopefully someone else will assist

Cheers

Gibbo

[sjoyce115]

Hi:

I seemed to have got lost in this hi-tech forum. Attached is my sample worksheet. The check numbers are not been deleted from sheet1. Can someone take a look?

Gbbo1715, your instructions are impressive.

Thanks

[sjoyce115]

Hi:

I finally figured this out. I used an option button instead of a command button.

Gibbo1715 and bodhi, thanks for all you help with this.
:beerchug: