Copy/Paste and Remove Duplicates

[Hudco]

Hi Peoples,
I have constructed a macro to copy a column of data, paste to a new sheet and then remove duplicates. Everything worked fine until I tried to amend the code to paste just the values and not the formats. Also the remove duplicates removes the formats in the new sheet after the data finishes e.g. if original data is 50 rows and with duplicates removed becomes just 20 rows, the previous rows (21 to 50) will have their formats removed.

Code as follows:
Private Sub CommandButton3_Click()
Sheet3.Select
Range("A6", Cells(Rows.Count, "A").End(xlUp)).Copy
Sheet1.Activate
ActiveSheet.Range("A6").PasteSpecial Paste:=xlPasteValues
Application.Selection.RemoveDuplicates Columns:=1, Header:=xlNo
Range("A6", .Range("A6" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
ActiveSheet.Range("A6").Select
End Sub

The macro stops at the command ".Rows.Count" in the 7th line.

Can anyone help?
Thanks

 

[pike]

hello,
should that line read,,

Range("A6", .Range("A" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

 

[Hudco]

Thanks Pike,
Just altered the line (i.e. "A6" to "A") but it still stops at ".Rows.Count"

As I said before, everything worked fine until I added in "Paste:=xlPasteValues"

Cheers

 

[Fluff]

Try

Range("A6", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

 

[Hudco]

Thanks Fluff,
Previously just the ".Rows.Count" would be highlighted as an error in the macro. Now the whole line is highlighted as an error. But it did seem to activate through to the Delete part, just failed at the "Shift:=xlUp".

So I removed the line altogether and the macro now works except the cells where duplicates are removed from lose all their formats which are lines.

I can live with that but if any solution is still possible would be grateful.

Thanks, Clyde

 

[pike]

Hello,
it looks like a row count problem
the original code need .row but really required the row count befor remove dupes ran

Option Explicit

Private Sub CommandButton3_Click()
Dim lngRow As Long
With Sheet3
 .Range("A6", .Cells(.Rows.Count, "A").End(xlUp)).Copy
End With
 Sheet1.Activate
 Range("A6").PasteSpecial Paste:=xlPasteValues
 lngRow = Range("A" & Rows.Count).End(xlUp).Row
 Application.Selection.RemoveDuplicates Columns:=1, Header:=xlNo
 'Range("A6", Range("A" & Rows.Count).End(xlUp).row).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
 Range("A6", Range("A" & lngRow)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
 Range("A6").Select
 End Sub

 

[Hudco]

Thanks Pike

 

[pike]

it removed the formatted cells or do you need the entire row?

 

[Hudco]

Thanks Pike, it worked but formats were not restored on cells that were cleared. However I found a solution by copying formats of cells in adjacent row which are identical.