A week ago I posted the question, Excel Macro to compare and extract item: link below:
https://www.techrepublic.com.com/5208-6239-0.html?forumID=52&threadID=135486&start0
abcomp282@yahoo.com.au posted an elegant solution.
However I have hit what seems to be a row limit. The largest number I can enter in the “Enter the bottom Row:” box is 32767. If I enter anything larger I get the message to enter a number only. I have a list that is over 55000 rows long.
Here is the macro that abcomp282 posted.
Sub mcrRemoveDupes()
Dim intCol As Integer
Dim intCount As Integer
Dim intMaxRow As Integer
Dim intReply As Integer
Dim intRow As Integer
Dim intStartRow As Integer
Dim strTemp As String
Dim strTest1 As String
Dim strTest2 As String
On Error GoTo errHandler
intCount = 0
intCol = 1
intReply = MsgBox(“Is the data to be cleaned in Column A and sorted?”, vbYesNoCancel + vbDefaultButton1 + vbQuestion, “Data Location”)
If intReply = vbCancel Then
Exit Sub
End If
If intReply = vbNo Then
intReply = MsgBox(“Ensure the data to be cleaned is in Column A, then sort the data in Ascending order. When you’re finished, run this macro again.”, vbDefaultButton1 + vbInformation + vbOKOnly, “Data Location”)
Exit Sub
End If
intStartRow = CInt(InputBox(“Enter the starting row:”))
If intStartRow < 1 Then
Exit Sub
End If
intMaxRow = CInt(InputBox("Enter the bottom row:"))
If intMaxRow < 1 Then
Exit Sub
End If
For intRow = intMaxRow To intStartRow + 1 Step -1
Range(Cells(intRow, intCol), Cells(intRow, intCol)).Select
strTest1 = ActiveCell.Value
strTest2 = ActiveCell.Offset(-1, 0)
If UCase(strTest1) = UCase(strTest2) Then
Rows(intRow).Delete
intCount = intCount + 1
End If
Next intRow
intReply = MsgBox("There were " & intCount & " duplicate rows deleted.", vbInformation + vbOKOnly, "Complete")
Exit Sub
errHandler:
intReply = MsgBox("Enter a number only", vbOKOnly + vbCritical + vbDefaultButton1, "Parameter Error")
End Sub