Numeric search and replace - lottery numbers! 1

[gdgrl]

I have been searching forums for over a month to help me do this. I thought it would be easy. I'm tired but hopeful.

Eg. File A - .xls - 1,2,3,4,5,6

I want to find duplicates and delete them all. Not just one
number but all six same numbers.

Is there a way to search batch files? and when found delete them. I have my original excel file with lottery numbers - then I have an excel elimination file I want to delete from my original.

File A has 1000 lines of numbers
File B has 800 lines of numbers

File A minus File B = 200 lines of lottery numbers I want to play.

Any help or referral is much appreciated.

 

[Dreamboat]

I have the goods you want in a file, I do believe.

This code will go through all of the values in ONE column, and delete ALL of the duplicates. In other words, if 6 records start with 12345, then all 6 records are deleted, NOT just the 5 duplicates.


Here's the code:
Sub DeleteDuplicateRows()

Dim rng As Range
Dim cl As Range
Dim rngOriginal As Range
Dim rngDups As Range
Dim strCol As String
Dim strRangeErr As String

strRangeErr = "Error with your range, please try again"

On Error Resume Next
Set rngOriginal = Selection
Set rng = Application.InputBox("Please select the range that you would like to " & _
"delete rows from - please make sure that you only select ONE column " & _
"in your range.", "Select Range", , , , , , 8)
If Err <> 0 Then
MsgBox strRangeErr, vbCritical, &quot;Exiting...&quot;
GoTo ExitHere
ElseIf rng Is Nothing Then
MsgBox strRangeErr, vbCritical, &quot;Exiting...&quot;
GoTo ExitHere
ElseIf rng.Columns.Count > 1 Then
MsgBox &quot;You selected a range that has more than one column - please &quot; & _
&quot;re-run this program and select only one column.&quot;, vbCritical, &quot;Exiting...&quot;
GoTo ExitHere
ElseIf rng.Rows.Count <= 1 Then
MsgBox &quot;There are no duplicates in one cell! Please try again and select more &quot; & _
&quot;than one cell.&quot;, vbCritical, &quot;Exiting...&quot;
GoTo ExitHere
End If
On Error GoTo HandleErr

Application.ScreenUpdating = False

rng.Range(&quot;A1&quot.Offset(0, 1).Select
Selection.EntireColumn.Insert
ActiveCell.Formula = &quot;=COUNTIF(&quot; & rng.Address & &quot;,&quot; & _
Application.ConvertFormula(rng.Range(&quot;A1&quot.Address, xlA1, xlA1, xlRelative) & _
&quot&quot;
Selection.AutoFill _
Destination:=Range(rng.Range(&quot;A1&quot.Offset(0, 1), _
rng(rng.Rows.Count, rng.Columns.Count).Offset(0, 1)), _
Type:=xlFillDefault

For Each cl In _
Range(rng.Range(&quot;A1&quot.Offset(0, 1), rng(rng.Rows.Count, rng.Columns.Count).Offset(0, 1))
If cl.Value > 1 Then
If rngDups Is Nothing Then
Set rngDups = Range(cl.Address)
Else
Set rngDups = Application.Union(rngDups, Range(cl.Address))
End If
End If
Next cl

rngDups.EntireRow.Delete
rng.Offset(0, 1).EntireColumn.Delete
If Not (rngOriginal Is Nothing) Then
rngOriginal.Select
Else
Range(&quot;A1&quot.Select
End If

Application.ScreenUpdating = True

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox Err.Description, vbCritical, &quot;Error in DeleteDuplicateRows&quot;
Resume ExitHere
End Select

End Sub

Anybody that wants the file, please ask for DeleteDupes.xls to Anne@TheOfficeExperts.com Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[JoyInOK]

If ever there was a worthy use of coding, gdgrl, this would be it! May be the only way a programmer gets a raise these days...Good luck playing the numbers!

 

[gdgrl]

O.K. I'm really impressed and also excited - now how do I insert this code in excel. Can anyone direct me to some info because I can't wait to try it out!!

 

[Dreamboat]

1- Start Excel
2- Open your file
3- Press Alt+F11 (VBE is opened)
4- Click Insert_Module (You have a new module)
5- Put this code in that module
6- Press F5 or Goto Excel Tools_Macro_Macros, select this macro name in macro list and Run. Or put a command button on sheet and assign this macro (make your button Don't move or size with cells)

Compliments of my best friend, Suat. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[gdgrl]

I have tried several times to insert this code exactly to instructions and still receive
&quot;Compile error:
Expected: end of statement

I do not know enough to fix it.

I still don't want to give up. Any more help?

 

[Edrondol]

Did you type the code in or did you copy/paste? I copy/pasted and it worked fine. If you typed it, it sounds like you missed a statement. Possibly the final End Sub?




-Dave

 

[gdgrl]

I copied and pasted it exactly from Sub DeleteDuplicateRows() to End Sub. I'm glad it worked for you. That's promising. I will try again.

 

[Dreamboat]

Yep.
Expected end of statement means somethings wrong.

Listen. Don't forget I can send you the Excel file, or just the code in a plain-old, safe text file. My email's in one of the posts above. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[gdgrl]

I'm soooo frustrated because I really want this to work.
I get compile error:
syntax error now all the time.

It highlights this code in red:

Set rng = Application.InputBox(&quot;Please select the range that you would
like to &quot; & _
&quot;delete rows from - please make sure that you only select ONE
column &quot; & _
&quot;in your range.&quot;, &quot;Select Range&quot;, , , , , , 8)


please &quot; & _
&quot;re-run this program and select only one column.&quot;, vbCritical,
&quot;Exiting...&quot;

select more &quot; & _
&quot;than one cell.&quot;, vbCritical, &quot;Exiting...&quot;
ActiveCell.Formula = &quot;=COUNTIF(&quot; & rng.Address & &quot;,&quot; & _
Application.ConvertFormula(rng.Range(&quot;A1&quot.Address, xlA1, xlA1,
xlRelative) & _
&quot&quot;

 

[Dreamboat]

YES!
You see how after the word &quot;would&quot; it breaks to a new line?
No can do. Just click in front of &quot;Set rng =&quot; and then hit your End key. Then hit your delete key to bring &quot;like&quot; right behind &quot;would&quot;. That line needs to end with the underscore after the ampersand. It cannot break to the next line as it is above.

Same with the please, etc...

Sometimes, it is easier to copy code and paste it into notepad, and then copy and paste it from there. Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[gdgrl]

Your getting me excited again Dreamboat. I'm going to do as exactly as you say.

 

[gdgrl]

I'm very tired. Enough for tonight. If you can email the code to kkeaney@sympatico.ca I will try again in the morning. I just don't understand
why I keep receiving these syntax errors when I paste it in it looks exactly like above.
Thankyou.

 

[gdgrl]

Thank you soooooooo much Dreamboat for emailing me the code!

 

[Dreamboat]

Sure! Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX