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 formatti...

Sample file.xlsx13 KB

Macros and VBA

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

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

Forum Discussion

Help on VBA code for formatting the sheet

Resources