Find Duplicates

[adam]

Code: Select all

Set sCell = ("datarange")

I have created a data range by using the following line giving the above name.but no success.

Code: Select all

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),7)

[HansV]

"datarange" is a string. Once again, you cannot set sCell to a string.

Moreover, you don't want to set sCell to a range consisting of multiple cells; you should set it to a range consisting of a single cell only (A2).

[adam]

I did set it as =Data!$A$2 with as but it is still the same. may be I dont know what I'm doing

[HansV]

=Data!$A$2 is a worksheet formula, not VBA.

Adam, you have used code that sets a range many times in the code that you obtained from search engines or from forums such as this one. You should not only use the code, but try to understand it, so that you'll know how to use it in a different situation. Otherwise, you'll have to ask about each little detail over and over again.

If you're not able or willing to attend a course on programming Excel, you should study a book about it. John Walkenbach has written a series of excellent books about Excel and Excel VBA. See The Spreadsheet Page - Excel Books by John Walkenbach.

To end your predicament, here is the single line you need to set sCell to the range consisting of cell A2:

Set sCell = Range("A2")

[adam]

Atlast, thanks for the help Hans. If you had let me know the five letter word; "Range" it would have been over now because at first I did try using Set sCell = "A2"
Anyways, thanks for the help & support. I do really appreciate it.
You had made a difference.

[HansV]

I'm trying to get you to think for yourself, Adam. I mentioned the word "Range" in Post=17186 and again in Post=17188 but you didn't pick up the hint...

[adam]

Oh! Sorry for me being so dumb. I hope it wont happen again.

[adam]

Thanks for the help Hans. Finally the code highlights the duplicate values from the list box. But why doesn't the highlighted rows get filtered in the list box same as in the worksheet? so that it would make the user to view only the highlighted rows.

A list box whose RowSource is a range doesn't work that way: even if some of the rows in the range are hidden, they remain visible in the list box. You can't hide items in a list box.

Based upon the above quotes, I have added a list box2 to my user form in order to view the filtered data or the duplicate rows that are filtered by the code. Moreover, I have added a new button to the form with the name “Search Dups”.

As the user form is loaded, the list box2 is invisible and the list box1 is visible with the populated data from the sheet “Data”.

When the command button “Find Dups” is clicked, the list box1 gets hidden and the listbox2 gets visible with (filtered) populated data from the sheet “NewSheet”
When the user clicks this button all the data with the same Customer ID from the column “A “of the sheet “Data” will get filtered to the list box 2 from the sheet “NewSheet”, which gives the feature for the user to view the highlighted rows.

When the user clicks the button “Del Dups” all the duplicate values (with the code Call Find_RemoveDuplicates) in the sheet gets deleted making the list box 2 invisible and the listbox1 visible again.

What I'm stuck in is that I cant load the userform & it gives me the message "Variable not defined" by highlighting the row

Code: Select all

Data = Sheets("Data").Range("A1").CurrentRegion.Offset(1)

Any suggestion or help to overcome this would be kindly appreciated.

I have attached my workbook for your reference.

Thanks in advance.

[HansV]

Your code tries to assign a value to the variable Data:

Data = ...

However, you haven't declared the variable Data. You must add a line of the form

Dim Data As Variant

or similar - see the workbook(s) that you attached to the thread Filter Data In User Form. You did declare the variable Data there, but apparently you forgot to copy the relevant line of code.

[adam]

or similar - see the workbook(s) that you attached to the thread Filter Data In User Form. You did declare the variable Data there, but apparently you forgot to copy the relevant line of code.

I've tried by adding the line
Public Data As Variant
also by
Dim Data As Variant
But now the I get the type mismatch run time error message.
Any suggestions please

[HansV]

The line

Me.cmdFindDups.Value = Application.Transpose(z)

tries to assign a value to a command button. A command button doesn't have a value, so this instruction makes no sense. In the previous workbook, the corresponding line assigned the list of a combo box. Please have a look there again.

[adam]

I did had a look there. So I did try by changing the previous Me.ComboBox1.List = Application.Transpose(z)
in this workbook by Me.cmdFindDups.List = Application.Transpose(z) Which again as you say didn't make any sense.

[HansV]

Do you want to do something with the list of unique values at all?
If so, you should assign the list to a combo box or list box.
If not, you can remove the part of the code that builds the list of unique values.

[adam]

The purpose of the list box 2 is for the user to be able to see the list of duplicate values when he clicks the button Find Dups. the two listboxes are to be placed on top of each other so that when one is visible the other gets hidden.

Actually the listbox 2 would not show unique values it will show the duplicate values.

How could I remove the part of the code that builds the list of unique values.

[HansV]

Study the code and try to understand how it works. You will then know what to remove.

[adam]

You had sent me to the study room many times with this code. And I did try to solve it for myself without disturbing you. But since I couldn't solve I came back to you.

[HansV]

I'm sorry, that would amount to me writing all the code. I'm not going to do that; it is beyond the scope of a discussion forum in my opinion.

[adam]

Hans, I do agree with your opinion about the limitations of a discussion forum & I hope I wasn't asking for a brand new code. But instead, to point out me the line where I should start & end to "remove the part of the code that builds the list of unique values" from the code that; "I had embedded in the workbook uploaded in the Post=18123

[HansV]

That wouldn't work, Adam, as I've explained before.

[adam]

Depending upon the code posted at Post 20392 I have assigned two command buttons in my user form. When the command button Find Dups is clicked it calls the remove dups macro which filters the sheet with duplicate values but the list box still does not get filtered and I get debug message highlighting the line
frmRemoveDuplicates.ListBox1.Selected(itm) = True

What may be the reason for this?