Help on VBA code for formatting the sheet

[Dinesh0911]

Hello Everyone, Greetings to all..

Requesting for your help on below scenarios where I am finding difficulty for the solutions.

First of all I have attached the Sample file and expected formatting I need, Kindly have a look first.

Scenario 1:- If we have duplicate values in  Column A, I need to keep the first duplicate value and other cells to blank(row should not be deleted).

Scenario 2:-Can we give the numbering to the rows based on the data we filled?

For an example, in software testing test cases, we have 9 steps, in such case can we over write the step name to 1-9?, similarly if we have another case having only 4 steps, can we overwrite the data in the step name column to 1-4?

Please have a look in the sample file I attached.

@Hans Vogelaar , Please have a look if you got some time.

Special thanks to you for helping my previous request.

Thank you in advance, Have a great day.

[#2 · Accepted solution]

@Dinesh0911 

Like this:

Sub FixRange2()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A:E").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = m To 2 Step -1
        Range("B" & r).Value = Application.CountIf(Range("E2:E" & r), Range("E" & r).Value)
        If Range("E" & r).Value = Range("E" & r - 1).Value Then
            Range("A" & r).ClearContents
            Range("E" & r).ClearContents
        End If
    Next r
    Application.ScreenUpdating = True
End Sub