Deleting Details in a Sub total formula

[da]

Hi
Below data is a result of sub-total function. Is there anyway I can delete
all the detail rows associated with each total and show only the data as
shown below? when I try to copy and paste, all the detail rows show up on the
worksheet. I want to show only the sub-totals (totals). There are about 300+
rows which I want to delete without deleting sub-totals.
thank you


Sender CC Hours Amount-B
1160016 Total 153.50 $9,410.02
1160018 Total 2,835.75 $156,925.78
1160020 Total 311.00 $11,510.42
1160021 Total 136.00 $7,792.80
1160026 Total 533.00 $21,479.99
1160027 Total 384.00 $25,361.06
1160029 Total 46.00 $3,034.16
1160032 Total 43.50 $3,291.21
1160035 Total 28.00 $1,986.18
1160068 Total 76.00 $4,673.15
1160076 Total 273.00 $17,753.52
1160077 Total 222.50 $13,648.69
1160080 Total 119.00 $6,881.42
1160081 Total 158.00 $9,116.12
1160090 Total 14.50 $987.27
1160095 Total 393.00 $25,474.11
1160102 Total 42.00 $2,476.74
1300034 Total 446.00 $16,633.76
1300035 Total 130.00 $6,134.20
1400008 Total 296.50 $14,296.78

 

[Dave Peterson]

How about copying the visible rows to a different/new worksheet?

Hide the details
select the range to copy
F5 (or ctrl-g or Edit|Goto)
Special|Visible cells only

Then copy|paste to the new location.

And maybe select that first column
edit|replace
what: Total_ (with that trailing space character)
with: (leave blank)
replace all



But...

This sounds like a perfect opportunity to learn about pivottables. You can
create this kind of summary table pretty easily.

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Shane Devenshire]

Hi,

If you hide the detail row using AutoFilter then you can copy the diplayed
range and paste it as values somewhere else. Paste as values because you
don't want to take the formula. In this case I would filter on the Sender CC
column since it contains the word Total. I would open the AutoFilter drop
down and choose Custom, pick Contains from the first box and enter Total in
the second box to the right.

When you copy and autofiltered range only the visible cells are copied
automatically.