SOLVED

Select every N-th row and replace column values

%3CLINGO-SUB%20id%3D%22lingo-sub-1827195%22%20slang%3D%22en-US%22%3ESelect%20every%20N-th%20row%20and%20replace%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1827195%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20new%20to%20VBA%20and%20macro%20so%20I%20need%20help%20with%20a%20script%2Fcode%20that%20can%20select%20every%2018th-21th%20row%20and%20replace%20column%20%22I%22%20with%20the%20following%20values%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3E%3CSPAN%3EParan%C3%B8tter%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%3CSPAN%3EPekann%C3%B8tter%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%3CSPAN%3EPistasjn%C3%B8tter%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E%3CSPAN%3EValn%C3%B8tter%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eand%20replace%20column%20%22L%22-%22O%22%20to%20blank%20(should%20be%20no%20values).%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20should%20be%20a%20loop.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20attached%20a%20part%20of%20my%20dataset%20and%20highlighted%20the%20rows%20and%20columns%20I%20want%20to%20change.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20is%20a%20bit%20urgent%20so%20I'm%20really%20trying%20to%20automate%20this%20process.%20Thanks%20for%20any%20help%20you%20can%20provide!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1827195%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1827557%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20every%20N-th%20row%20and%20replace%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1827557%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20EditRows()%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%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20For%20r%20%3D%2019%20To%20m%20Step%2021%0A%20%20%20%20%20%20%20%20Range(%22I%22%20%26amp%3B%20r).Value%20%3D%20%22Paran%C3%B8tter%22%0A%20%20%20%20%20%20%20%20Range(%22I%22%20%26amp%3B%20r%20%2B%201).Value%20%3D%20%22Pekann%C3%B8tter%22%0A%20%20%20%20%20%20%20%20Range(%22I%22%20%26amp%3B%20r%20%2B%202).Value%20%3D%20%22Pistasjn%C3%B8tter%22%0A%20%20%20%20%20%20%20%20Range(%22I%22%20%26amp%3B%20r%20%2B%203).Value%20%3D%20%22Valn%C3%B8tter%22%0A%20%20%20%20%20%20%20%20Range(%22L%22%20%26amp%3B%20r).Resize(4%2C%204).ClearContents%0A%20%20%20%20Next%20r%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F845723%22%20target%3D%22_blank%22%3E%40ushbar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1831583%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20every%20N-th%20row%20and%20replace%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1831583%22%20slang%3D%22en-US%22%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%3C%2FP%3E%3CP%3EThanks%20a%20lot!%20That%20saved%20me%20so%20much%20time!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20one%20last%20thing.%20Do%20you%20know%20a%20code%20for%20how%20to%20move%20every%205th%20row%20to%20the%2021th%20row%3F%20I%20want%20to%20move%20the%20row%20with%20%22Pean%C3%B8tter%22%20to%20the%20last%20row%20of%20each%20unique%20product%20id%20(see%20column%20A).%20I%20want%20all%20the%20nuts%20(n%C3%B8tter)%20to%20be%20in%20the%20same%20order.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, 

 

I'm new to VBA and macro so I need help with a script/code that can select every 18th-21th row and replace column "I" with the following values: 

Paranøtter
Pekannøtter
Pistasjnøtter
Valnøtter

and replace column "L"-"O" to blank (should be no values). 

This should be a loop.

 

I have attached a part of my dataset and highlighted the rows and columns I want to change.

 

This is a bit urgent so I'm really trying to automate this process. Thanks for any help you can provide!

4 Replies
Best Response confirmed by ushbar (Occasional Contributor)
Solution

 

Sub EditRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = 19 To m Step 21
        Range("I" & r).Value = "Paranøtter"
        Range("I" & r + 1).Value = "Pekannøtter"
        Range("I" & r + 2).Value = "Pistasjnøtter"
        Range("I" & r + 3).Value = "Valnøtter"
        Range("L" & r).Resize(4, 4).ClearContents
    Next r
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

@ushbar 

@Hans Vogelaar 

Thanks a lot! That saved me so much time!

 

Just one last thing. Do you know a code for how to move every 5th row to the 21th row? I want to move the row with "Peanøtter" to the last row of each unique product id (see column A). I want all the nuts (nøtter) to be in the same order.

@ushbar 

Sub MovePeanøtter()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = 6 To m Step 21
        Range("A" & r).EntireRow.Cut
        Range("A" & r + 17).EntireRow.Insert
    Next r
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
thanks a lot!