Aug 20 2021 05:01 AM
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.
Aug 20 2021 05:17 AM
SolutionLike 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
Aug 20 2021 05:17 AM
SolutionLike 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