Help! Can’t debug macro used to delete duplicate rows

[Monomeeth]

Hi Everyone

I have a macro which works really well, except if the user decides to cancel
it. The macro is designed so that users can delete duplicate rows based on
the cell contents of between 1 and 5 columns.

For instance, if a user wants to simply delete all duplicates based on
Employee Number and column E contains the Employee Numbers, the user runs the
macro and types E into the input box and the macro simply goes down column E
and deletes any rows containing duplicates in that column. The user can
select more than one column, for instance typing in E,F,J means the macro
would check for any rows with duplicates based on cells in all three columns
matching.

The problem is that once Users run the macro, if they decide to cancel, it
actually still runs and deletes every single row containing data. I can't
seem to find the problem, hence my seeking your help!



The code is below:




Sub DeleteDuplicatesUpTo5Columns()

Dim Col As Variant
Dim ColNum As Long
Dim LastRow As Long
Dim Response As String
Dim RowCount As Long
Dim SelectCols As Variant

Application.ScreenUpdating = False

Response = InputBox("Enter up to 5 Column Letters to compare, seperated by
commas" & vbCrLf & "[e.g. A,D,E]")

SelectCols = Split(Response, ",")

'covert column letters to numbers
For Each Col In SelectCols
ColNum = Val(Range(Trim(Col) & "1").Column)
Col = ColNum
Next Col


LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Add row number to each row
For RowCount = 1 To LastRow
Range("IV" & RowCount) = RowCount
Next RowCount

'sort by each column
For Each Col In SelectCols
Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=Cells(1, Col), _
order1:=xlAscending
Next Col

For RowCount = LastRow To 2 Step -1
Match = True
For Each Col In SelectCols
If Cells(RowCount, Col) <> Cells(RowCount - 1, Col) Then
Match = False
Exit For
End If
Next Col

If Match = True Then
Rows(RowCount).Delete
End If
Next RowCount

'return order to original order
Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=Range("IV1"), _
order1:=xlAscending
'delete column with row numbers
Columns("IV").Delete

Application.ScreenUpdating = True
End Sub

 

[OssieMac]

When you say if the User decides to cancel. Do you mean Cancel at the
InputBox? if so then insert the following after the InputBox.

Note that a space and underscore at the end of a line is a linebreak in an
otherwise single line of code.

If Response = "" Then
MsgBox "No input or cancelled." & vbLf & vbLf & _
"Processing terminated."
Exit Sub
End If

As a matter of interest the following code does not do anything. The
contents of the array are still alpha after running it.

For Each Col In SelectCols
ColNum = Val(Range(Trim(Col) & "1").Column)
Col = ColNum
Next Col
'**************************************
'Can test with following
Dim i As Long
For i = LBound(SelectCols) To _
UBound(SelectCols)

MsgBox SelectCols(i)
Next i

'***************************************

However, there should be no need to convert the column Id to numeric. The
following code is perfectly valid.
Cells(Rows.Count, "A") is the same as
Cells(Rows.Count, 1)

Having said that, if you want to convert it to numeric, then the following
code will do it.

For i = LBound(SelectCols) _
To UBound(SelectCols)

SelectCols(i) = Columns(Trim(SelectCols(i))) .Column
Next i

Use same test as above.

Hope it helps and that the question I have answered is what you were asking
but if not then feel free to get back to me.

 

[K_Macd]

You may also disable the 'break' key but this should only be considered after
you have debugged the code considerably and thought about all the likely
errors which could mean killing Excel from the Windows Task Manager as the
only solution to terminate the macro.