remove duplicates and delete empty cells

Hi there,

I have a set of data in one column of "Sheet 1". I want to remove the duplicates and delete all empty cells. However this set of data with removed duplicates and deleted cells shall then be posted in "Sheet 2". The first set of data must be in cell B6. The title must not be shown. In addition the cells in Sheet 2 must not have any formatting.

The button to activate the duplicate remover and empty cell deleter shall be on a third tab "Sheet 3".

Attached is an excel file which shows the task and the desired output.

I am sure this is pretty basic but please help me as I am an absolute beginner regarding VBA.

Thanks in advance.

It must be a macro...

I have a code (below) but it doesn't fully work the way I like it to.

Sub Unique_List()
Dim ws1 As Worksheet: Set ws1 = Sheets("sheet1")
Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")

Application.ScreenUpdating = False
ws1.Range("B1:B" & ws1.Range("B" & Rows.Count).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws2.Range("B1"), Unique:=True
With ws2
.AutoFilterMode = False
.Range("B1:B" & .Range("B" & Rows.Count).Row).AutoFilter 1, "="
.Range("B2:B" & .Range("B" & Rows.Count).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
Application.ScreenUpdating = True

End Sub

Alternative

Please Login or Register  to view this content.

Seems to work, thanks. can you please send me the code so that if a filter set in sheet1 it still applies to all the hidden values as well?

thanks so much in advance.

It should, just test it for yourself.