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!