vba to insert a blank row at a change in value in col 3 only if not divisble by 8

  • I have the below code that will insert a blank row into my data based on a change in value in column 3, i need to alter this to look at column 3 and at a change in value count the number of rows above it and if divisible by 8 leave it as it is if not divisible by 8 then add enough blank rows so it is.


    I want to use the data I have as a mail merge document into a label template that has 8 labels per page. and want to ensure that at a change in value in column 3 it goes onto another sheet of labels and this is the only way I can think of doing it other than scrolling down counting the rows and manually adding blank rows on a worksheet that has 1910 rows of data


    Insert blank row at a change in value in column 3



  • Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8


    This need to go in the sheet code


  • Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8


    hi


    many thanks for your response, im a complete novice at VBA do i need to add the above code you have given me somewhere into my original code ?

  • Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8


    The sheet you want that code to execute from, it needs to be there rather than a module. Its difficult to know about your current code as id have to see it all...

  • Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8


    Quote from M40wen;793599

    The sheet you want that code to execute from, it needs to be there rather than a module. Its difficult to know about your current code as id have to see it all...


    Thank you for your support and patience but i cannot get this to work, I have opened my sheet and in the editor i have selected worksheet as the object and change as the procedure


    then copied in the below code but when i click on run it asks me to name a macro ?? i have tried using the Worksheet_Change as the name but it just opens a new window for a macro apologies can i ask for more direction please


  • Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8


    No, you don't need to run it - you need to make a change in column 3 (ie column C) on that sheet. The code is saying if you make a change in Column C, and the row changed is divisible by 8 or not, then insert lines...


    Try it..

  • Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8


    thank you again, you are most patient. i see now what this is doing and indeed it does exactly as you say, thank you. however this is not what i am looking for i probably wasn't clear in my explanation apologies.


    i have a broadsheet of data that i don't want to change in Column three i have class codes that will repeat for so many rows then change value. what i want to do is run a code that will based on col 3 identify where the value changes and then count the rows where the value matches above it and if divisible by 8 leave it as it is and if not divisible by 8 then add enough blank rows so that is it before the next value in that column.


    this is so that when i use this excel sheet as a mail merge source for a sheet of labels ( 8 per page) i wont get multiple class codes on the same sheet of labels, i will get the one class and any blanks that we have added on one sheet does that make sense is this possible ?

  • Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8


    Ahh I see - you aren't changing anything in the column, its the data value that's changing. QQ:-


    Is column C sorted already?


    Do you have headers?


    Does data start in row 2?



    What is the name of the sheet this process is on (I prefer to ref the sheet directly that's all)


    For the data, which column is the last data set in? Will it always be populated or is there blank details (im trying to identify what your last row of your data range is in)

  • Re: vba to insert a blank row at a change in value in col 3 only if not divisble by 8


    yes that's correct like i say my explanation most probably was to blame, would it help if i anonomised the data and sent or uploaded the file ?


    column C is sorted


    Yes there are headers (column titles)
    Yes the data starts in row 2
    sheet name is simply "Sheet1"
    column T is the last data column and it is not always populated i can delete it as that data is not required for the mail merge.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!