how can I modify this macro to work on another column besides "A"

zhead · #1 10-05-2015, 08:49 AM

Is it possible to modify this macro to work on any column? It seems to only work if my data is in column "A".

Sub luxation()
Dim N As Long
N = Cells(Rows.Count, 1).End(xlUp).Row

For i = N To 2 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then
Cells(i, 1) = ""
End If
Next i
End Sub

shg · #2 10-06-2015, 08:27 AM

One way:

Code:

Sub luxation()
  Dim r             As Range
  Dim iRow          As Long
  Dim iCol          As Long
 
  Set r = ActiveSheet.UsedRange
 
  For iCol = 1 To r.Columns.Count
    For iRow = r.Rows.Count To 2 Step -1
      If r(iRow, iCol).Value2 = r(iRow - 1, iCol).Value2 Then r(iRow, iCol).ClearContents
     Next iRow
  Next iCol
End Sub

zhead · #3 10-06-2015, 02:32 PM

Thanks for your reply. But I only want to remove duplicates in column "R" instead of "A"
So how do I select which column.

shg · #4 10-06-2015, 03:32 PM

Code:

Sub luxation()
  Dim i          As Long
 
  With Range("R:R")
    For i = .Cells(.Rows.Count).End(xlUp).Row To 2 Step -1
      If .Cells(i).Value2 = .Cells(i - 1).Value2 Then .Cells(i, 1).ClearContents
    Next i
  End With
End Sub

zhead · #5 10-06-2015, 04:10 PM

Thanks very much. That worked great.

shg · #6 10-06-2015, 05:04 PM

You're welcome.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N	mikey386	Excel	2	12-11-2014 01:14 PM
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas	jocke321	Word VBA	2	12-10-2014 11:27 AM
Modify "preset" animations in PowerPoint 2013	ialpawsv	PowerPoint	1	11-26-2014 02:23 PM
Outlook 2003, Modify default "entry type" for new journal entries	narichardson	Outlook	5	03-14-2012 08:34 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM