• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Excel, clearing contents based on data in the cells, reording data

A sample file is uploaded and I need all multiple appearing names to be cleared from their cells [leaving one occurance of each name].
The remaining unique names to be in the uppermost cell of its section...this is the part that I can't get.
The dup names under the first one -- these cells  should have their contents cleared.
Other column need to remain as-is. [No row deletions].
In the attached sample, for example, the name should appear on row 2 and be deleted from row 3-5.
[I tried removing dups in A column using Trim + Count, but the remaining Name is in the last row of its section
instead of the top row of its section.]  

All help appreciated.
  • 4
  • 3
1 Solution
Robberbaron (robr)Commented:
is it acceptable to sort the date first ?

paste this code into a module.  Run with the desired sheet active.
Sub RemoveDup()

    Dim rngAll As Range, rngSort As Range
    Dim lastval As String
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Set rngAll = Selection
    Set rngSort = rngAll.Columns(1)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=rngSort _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange rngAll
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With

    Set rngSort = Range("A1")
    'lastval = rngSort.Value
    'Set rngSort = rngSort.Offset(1, 0)
    lastval = "===========" 'dummy
    Do While rngSort.Value <> ""
        If rngSort.Value = lastval Then
            'delete the value
            rngSort.Value = ""
            lastval = rngSort.Value
        End If
        Set rngSort = rngSort.Offset(1, 0)

End Sub

Open in new window

Nice use of 'Sort' robberbaron. :)

dkoliver: I think this is what you want? Please check the sample file attached. I have colored the cells in 'Yellow' which will not be cleared? If that is correct, then run the Sub RemoveMultipleWords() in the Sample file attached.


Code Used

Sub RemoveMultipleWords()
    Dim i As Long, LastRow As Long
    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    For i = LastRow To 2 Step -1
        If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A2:A" & LastRow), _
        Sheets("Sheet1").Range("A" & i).Value) > 1 Then
           Sheets("Sheet1").Range("A" & i).ClearContents
        End If
End Sub

Open in new window

dkoliverAuthor Commented:
Yes, thanks, I should have said sorting the file is fine.  I will try this today and let you know.  Thanks so much!
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

dkoliver: Did you see the code that I posted? You might not need to sort...

dkoliverAuthor Commented:
Yes, thank you, I did see your code and prefer it since it is shorter. I have been left behind in the excel world -- pls. explain how to do this programming -- do I enter as a macro somewhere on the sheet and run it?  Sorry to ask but it's been a long time -- back to Lotus 1-2-3 since I did any macro writing. Thanks!
>>do I enter as a macro somewhere on the sheet and run it?  Sorry to ask but it's been a long time -- back to Lotus 1-2-3 since I did any macro writing. Thanks!

Please create a module and paste the code in the module.

Press Alt +F11 from the worksheet and in the VBE and check the module that I posted. You can then run the macros.

Perhaps later you might want to see this link to refresh on VBA :)


dkoliverAuthor Commented:
Thank you so much!
dkoliverAuthor Commented:
Very elegant way to handle the issue.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now