parscon
01-09-2014, 01:40 PM
i have this VBA code ,
if will check all column of sheet 1 from A1:Z and if find duplicate data on sheet 2 it will delete that row on sheet 2 and the problem is this code will check only column A on sheet2 , i need to check all column on sheet 2 and i know there must be something in this section of code
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
could you please help to find a solution to check all column of sheet2 also .
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:Z" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
' Loop through all records in the second list.
For iCtr = iListCount To 1 Step -1
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).EntireRow.Delete
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub
if will check all column of sheet 1 from A1:Z and if find duplicate data on sheet 2 it will delete that row on sheet 2 and the problem is this code will check only column A on sheet2 , i need to check all column on sheet 2 and i know there must be something in this section of code
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
could you please help to find a solution to check all column of sheet2 also .
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:Z" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
' Loop through all records in the second list.
For iCtr = iListCount To 1 Step -1
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).EntireRow.Delete
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub