Remove Duplicates and Blanks in Range

[redbaron06]

Hi all,

I have been fiddeling with this for a few day with no substantial result. I would like to be able to do exactally what the "Remove Duplicates" function on the data tab in Excel 2007 does - remove duplicates, compress the data (or remove the blanks) and sort the data within a given range.

I need to do so through VBA, without deleting whole rows. Any Suggestions. I have looked through previous selections and know this is at least partially possible without removing the blanks using:

Sub Remove_Duplicates()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If WorksheetFunction.CountIf(Columns("A"), Range("A" & i).Value) > 1 Then Rows(i).Delete
Next i
End Sub

Thanks any help is appreciated!

 

[Weaver]

Does the new dataset have to replace the original?

If not, try recording yourself using the advanced filter option, as this has a function which allows you to obtain a subset of your data with unique values only, which essentially is what you're after.

You could always clear the old dataset and overwrite with the new one afterwards if necessary.

 

[redbaron06]

Hi Weaver,

I suppose it does not. It could be displayed in another column, that would be fine. I would like to avoid performating any three tasks: sorting, deleting duplicates, and condensing, manually as there will be other vba functions and formulas that will refer to this list, and this will be designed to be used by others.


The dataset is the result of information generated using closed workbooks (aka a macro that opens them copies the data and closes them). In order to do so, I spaced the data out in incriments of 200 cells, which leaves many blank cells. The information contains many duplicates, which I would like to weed out to create a simple list for the user and for some countif formulas that refer to the original data.