Need help modifying existing macro to auto count cells

  • Hello,


    I have a large spreadsheet where I use the following macro to insert a blank line after every unique entry. The macro works quite well, but I would like it to go a little further and count the number of entries and note that in the newly inserted line. The following table I help illustrate what I have in mind:
    column1 | column2 | column3
    ----------------------------------
    A
    | small | blue
    A
    | small | orange
    A
    | small | yellow
    B
    | med | yellow
    B
    | med | blue
    C
    | large | green
    D
    | large | green Using the macro the result is
    column1 | column2 | column3
    ----------------------------------
    A
    | small | blue
    A
    | small | orange
    A
    | small | yellow


    B | med | yellow
    B
    | med | blue


    C | large | green
    Macro is below:


    sub AddBlankRows()
    '
    dim iRow asinteger, iCol asinteger
    dim oRng as range


    set oRng=range("a1")


    irow=oRng.column
    icol
    =oRng.column


    do
    '
    if cells(irow+1, iCol)<>cells(irow,iCol)then
    cells
    (irow+1,iCol).entirerow.insert shift:=xldown
    irow
    =irow+2
    else
    irow
    =irow+1
    endif
    '
    loopwhilenot cells (irow,iCol).text=""
    '
    endsub


    Please note that I did not write this macro, but it has helped me tremendously. I would like to expand this macro such that the end result is similar to below:

    column1 | column2 | column3
    ----------------------------------
    A
    | small | blue
    A
    | small | orange
    A
    | small | yellow
    Total: 3
    B
    | med | yellow
    B
    | med | blue
    Total: 2
    C
    | large | green
    Total: 1
    Please notice the addition of "totals" using count or counta function.


    Thank you!








  • Re: Need help modifying existing macro to auto count cells


    Based on your existing code, I'd do it like this

  • Re: Need help modifying existing macro to auto count cells


    Thank you very much NoSparks! the code worked quite well! I am very grateful for your help. You saved me a lot of time and tedious repetitive work! thank you! thank you! thank you!

Participate now!

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