Forum Discussion

Dinesh0911's avatar
Dinesh0911
Copper Contributor
Aug 20, 2021
Solved

Help on VBA code for formatting the sheet

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.

HansVogelaar , 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.

 

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Aug 20, 2021

    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

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 20, 2021

    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

Resources