On Jul 12, 4:46 am, "michael.beckinsale"
wrote:
Hi Bob,

I tried that but the filter does not seem to work properly while the
data/subtotals are applied.

I also tried same and then converting helper column to values rather
than formulas & removing subtotals but of course that removes the rows
the subtotals were on!

I am really struggling with this, is there some way the subtotals can
be inserted by VBA without the grouping action performed using data/
subtotals? It wouldn't be the perfect solution but l could work with
the result to get what l want.

You might try a macro something like this:

sub addTotals()
dim currRow as long
dim startRow as long
dim blankCount as integer

blankCount=0
currRow=1
startRow=1
While blankCount < 4
if (isempty(cells(currRow,1))) then
blankCount=blankCount+1
else
blankCount=0
end if
if (cells(currRow,1)<cells(currRow+1,1) then
Rows(currRow+1).Insert shift:=xlDown
cells(currRow+1,7)="=sum(G"+format(startRow)
+":G"+format(currRow)+")"
if (cells(currRow+1,7).value = 0) then
rows(format(startRow)+":"+format(currRow)).hidden= true
end if
currRow=currRow+1
startRow=currRow+1
end if
currRow=currRow+1
wend
end sub

This will insert a new row every time column A changes and only put a
sum formula into Column G on that row. If the value in the sum is 0
it will hide all the rows for that column A group. Since you said you
have 35000+ rows, make sure that your row index variables are longs
not integers.

I don't know what your data looks like but you might also look at
using a pivot table on a different sheet using your data as source.

Peter