kevvukeka
06-04-2013, 10:26 PM
Hi All,
I have two separate ranges in two separate worksheets. I need to compare these two range and pick the unique ones in one range. I could write the below code and its working. But I want to know how to make this error proof. sometimes we receive numbers in text format or there are extra space at the end of numbers. how can I modify this code to handle such situations.
In the below code "Monthly additions" sheet basically contains the master data and "Monthly claims report" contains the subset of this master data. with the code I am deleting the item that are common to both these ranges and retaining the unique ones in master data.
Kindly suggest how to modify the below one to handle the above situations.
Thanks in advance.
Sub twocol()
Dim monthlywks As Worksheet
Dim biweeklywks As Worksheet
Dim x, y As Variant
Dim rng1 As Range, rng2 As Range
With Sheets("Monthly Claims Report")
Set rng2 = .Range("E2", .Range("E2").End(xlDown))
End With
With Sheets("Monthly_Additions")
Set rng1 = .Range("G2", .Range("G2").End(xlDown))
End With
For Each x In rng1
For Each y In rng2
If y.Value = x.Value Then
x.Value = ""
Else
'do nothing
End If
Next y
Next x
With Sheets("Monthly_Additions")
.AutoFilterMode = False
.UsedRange.AutoFilter
.UsedRange.AutoFilter field:=7, Criteria1:="="
End With
Sheets("Monthly_Additions").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.SpecialCells(xlVisible).Select
Selection.EntireRow.Delete
Sheets("Monthly_Additions").AutoFilterMode = False
Selection.End(xlUp).Select
End Sub
I have two separate ranges in two separate worksheets. I need to compare these two range and pick the unique ones in one range. I could write the below code and its working. But I want to know how to make this error proof. sometimes we receive numbers in text format or there are extra space at the end of numbers. how can I modify this code to handle such situations.
In the below code "Monthly additions" sheet basically contains the master data and "Monthly claims report" contains the subset of this master data. with the code I am deleting the item that are common to both these ranges and retaining the unique ones in master data.
Kindly suggest how to modify the below one to handle the above situations.
Thanks in advance.
Sub twocol()
Dim monthlywks As Worksheet
Dim biweeklywks As Worksheet
Dim x, y As Variant
Dim rng1 As Range, rng2 As Range
With Sheets("Monthly Claims Report")
Set rng2 = .Range("E2", .Range("E2").End(xlDown))
End With
With Sheets("Monthly_Additions")
Set rng1 = .Range("G2", .Range("G2").End(xlDown))
End With
For Each x In rng1
For Each y In rng2
If y.Value = x.Value Then
x.Value = ""
Else
'do nothing
End If
Next y
Next x
With Sheets("Monthly_Additions")
.AutoFilterMode = False
.UsedRange.AutoFilter
.UsedRange.AutoFilter field:=7, Criteria1:="="
End With
Sheets("Monthly_Additions").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.SpecialCells(xlVisible).Select
Selection.EntireRow.Delete
Sheets("Monthly_Additions").AutoFilterMode = False
Selection.End(xlUp).Select
End Sub