SOLVED

Help on VBA code for formatting the sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2668861%22%20slang%3D%22en-US%22%3EHelp%20on%20VBA%20code%20for%20formatting%20the%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2668861%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%20Greetings%20to%20all..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERequesting%20for%20your%20help%20on%20below%20scenarios%20where%20I%20am%20finding%20difficulty%20for%20the%20solutions.%3C%2FP%3E%3CP%3EFirst%20of%20all%20I%20have%20attached%20the%20Sample%20file%20and%20expected%20formatting%20I%20need%2C%20Kindly%20have%20a%20look%20first.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScenario%201%3A-%20If%20we%20have%20duplicate%20values%20in%26nbsp%3B%20Column%20A%2C%20I%20need%20to%20keep%20the%20first%20duplicate%20value%20and%20other%20cells%20to%20blank(row%20should%20not%20be%20deleted).%3C%2FP%3E%3CP%3EScenario%202%3A-Can%20we%20give%20the%20numbering%20to%20the%20rows%20based%20on%20the%20data%20we%20filled%3F%3C%2FP%3E%3CP%3EFor%20an%20example%2C%20in%20software%20testing%20test%20cases%2C%20we%20have%209%20steps%2C%20in%20such%20case%20can%20we%20over%20write%20the%20step%20name%20to%201-9%3F%2C%20similarly%20if%20we%20have%20another%20case%20having%20only%204%20steps%2C%20can%20we%20overwrite%20the%20data%20in%20the%20step%20name%20column%20to%201-4%3F%3C%2FP%3E%3CP%3EPlease%20have%20a%20look%20in%20the%20sample%20file%20I%20attached.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%2C%20Please%20have%20a%20look%20if%20you%20got%20some%20time.%3C%2FP%3E%3CP%3ESpecial%20thanks%20to%20you%20for%20helping%20my%20previous%20request.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%2C%20Have%20a%20great%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2668861%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2668898%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20VBA%20code%20for%20formatting%20the%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2668898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1133065%22%20target%3D%22_blank%22%3E%40Dinesh0911%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20FixRange2()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Range(%22A%3AE%22).Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20For%20r%20%3D%20m%20To%202%20Step%20-1%0A%20%20%20%20%20%20%20%20Range(%22B%22%20%26amp%3B%20r).Value%20%3D%20Application.CountIf(Range(%22E2%3AE%22%20%26amp%3B%20r)%2C%20Range(%22E%22%20%26amp%3B%20r).Value)%0A%20%20%20%20%20%20%20%20If%20Range(%22E%22%20%26amp%3B%20r).Value%20%3D%20Range(%22E%22%20%26amp%3B%20r%20-%201).Value%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20r).ClearContents%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22E%22%20%26amp%3B%20r).ClearContents%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20r%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

1 Reply
best response confirmed by Dinesh0911 (Occasional Contributor)
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