Hi,
I have numeric data in sheet4.range(A1:A)
I have also numeric data in sheet4.range(A1:A)
If with both range found duplicate data then don't run code and warn me with the help of msg box
If duplicate not found then run the code,
Thanx alot
Hi,
I have numeric data in sheet4.range(A1:A)
I have also numeric data in sheet4.range(A1:A)
If with both range found duplicate data then don't run code and warn me with the help of msg box
If duplicate not found then run the code,
Thanx alot
Re: If duplicates found then don't run macro
Hi haroon2015,
Try this:
Option Explicit
Sub Macro1()
Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim rngCell As Range
Dim i As Integer
Dim rngMyRange As Range
Application.ScreenUpdating = False
Set wsSheet1 = Sheets("Sheet1") 'First sheet name with data. Change to suit.
Set wsSheet2 = Sheets("Sheet2") 'Second sheet name with data. Change to suit.
With CreateObject("Scripting.Dictionary")
For i = 0 To 1
If i = 0 Then
Set rngMyRange = wsSheet1.Range("A2:A" & wsSheet1.Range("A" & Rows.Count).End(xlUp).Row) 'Works down from cell A2. Change to suit.
Else
Set rngMyRange = wsSheet2.Range("A2:A" & wsSheet2.Range("A" & Rows.Count).End(xlUp).Row) 'Works down from cell A2. Change to suit.
End If
For Each rngCell In rngMyRange
If Len(rngCell.Value) > 0 Then
If Not .Exists(rngCell.Value) Then
.Add rngCell.Value, rngCell.Value
Else
MsgBox rngCell.Value & " already exists!!"
Exit Sub
End If
End If
Next rngCell
Next I
End With
'Code to run your macro here
Application.ScreenUpdating = True
End Sub
Display More
Regards,
Robert
Re: If duplicates found then don't run macro
thanx alot dear mod Trebor76
Its Working Properly but its taking too much time to find duplicate vaya msgbox
Re: If duplicates found then don't run macro
How many rows do you have as it was fast for me?
See if this makes a difference:
Option Explicit
Sub Macro1()
Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim rngCell As Range
Dim i As Integer
Dim rngMyRange As Range
Dim xlnCalcMethod As XlCalculation
With Application
xlnCalcMethod = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Set wsSheet1 = Sheets("Sheet1") 'First sheet name with data. Change to suit.
Set wsSheet2 = Sheets("Sheet2") 'Second sheet name with data. Change to suit.
With CreateObject("Scripting.Dictionary")
For i = 0 To 1
If i = 0 Then
Set rngMyRange = wsSheet1.Range("A2:A" & wsSheet1.Range("A" & Rows.Count).End(xlUp).Row) 'Works down from cell A2. Change to suit.
Else
Set rngMyRange = wsSheet2.Range("A2:A" & wsSheet2.Range("A" & Rows.Count).End(xlUp).Row) 'Works down from cell A2. Change to suit.
End If
For Each rngCell In rngMyRange
If Len(rngCell.Value) > 0 Then
If Not .Exists(rngCell.Value) Then
.Add rngCell.Value, rngCell.Value
Else
MsgBox rngCell.Value & " already exists!!"
With Application
.Calculation = xlnCalcMethod
.ScreenUpdating = True
End With
Exit Sub
End If
End If
Next rngCell
Next i
End With
'Code to run your macro here
With Application
.Calculation = xlnCalcMethod
.ScreenUpdating = True
End With
End Sub
Display More
Re: If duplicates found then don't run macro
35000 rows dear mod
Re: If duplicates found then don't run macro
In less rows its working perfectly
it will do
thanx a lot once again for hard work
Don’t have an account yet? Register yourself now and be a part of our community!