1819
03-04-2017, 12:23 PM
The small sample worksheet at the bottom has some duplicate URLs across the rows. The duplicates are coloured yellow.
I want to remove all duplicate values across the rows, keeping the remaining non-duplicate data in its rows. (Ideally, blank cells would be eliminated too, so there are still contiguous cell values across the rows).
I can find explanations about how to remove duplicate cell values entries down columns but not across rows:
http://www.bluepecantraining.com/portfolio/excel-vba-how-to-remove-duplicates-with-vba-macro/
https://www.mrexcel.com/forum/excel-questions/689604-visual-basic-applications-remove-duplicates.html
There is also this code in the section "VBA code: Copy and paste only non-blank cells in Excel" on the page https://www.extendoffice.com/documents/excel/1578-excel-copy-and-paste-only-non-blank-cells.html.
(Though I don't want to choose a range via dialogue box - I just simply want to remove all duplicates till the last row.)
Sub PasteNotBlanks()
Dim rng As Range
Dim InputRng As Range
Dim OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
If InputRng.Columns.Count > 1 Then
MsgBox "Please select one column."
Exit Sub
End If
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
InputRng.SpecialCells(xlCellTypeConstants).Copy Destination:=OutRng.Range("A1")
End Sub
Please could you suggest a VBA solution to remove all duplicate cell values across columns till the last row?
Many thanks.
I want to remove all duplicate values across the rows, keeping the remaining non-duplicate data in its rows. (Ideally, blank cells would be eliminated too, so there are still contiguous cell values across the rows).
I can find explanations about how to remove duplicate cell values entries down columns but not across rows:
http://www.bluepecantraining.com/portfolio/excel-vba-how-to-remove-duplicates-with-vba-macro/
https://www.mrexcel.com/forum/excel-questions/689604-visual-basic-applications-remove-duplicates.html
There is also this code in the section "VBA code: Copy and paste only non-blank cells in Excel" on the page https://www.extendoffice.com/documents/excel/1578-excel-copy-and-paste-only-non-blank-cells.html.
(Though I don't want to choose a range via dialogue box - I just simply want to remove all duplicates till the last row.)
Sub PasteNotBlanks()
Dim rng As Range
Dim InputRng As Range
Dim OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
If InputRng.Columns.Count > 1 Then
MsgBox "Please select one column."
Exit Sub
End If
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
InputRng.SpecialCells(xlCellTypeConstants).Copy Destination:=OutRng.Range("A1")
End Sub
Please could you suggest a VBA solution to remove all duplicate cell values across columns till the last row?
Many thanks.