Huge Worksheet

[Bill]

Hello,
I have a table that I filter and then copy to a new page. The problem is
that the table is only about 20 rows long when I filter it. But, when I
copy it to a new worksheet, the workbook becomes almost 3.5 MB in size. It
is like the workbook thinks there is something all the way down to row
65,000. The code I use is:

If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Branchname
Call FindtheCol(ColName) finds a spefiic column
NColl = ActiveCell.Column
Union(Columns(2), Columns(NColl)).Copy

Any ideas on how to avoid this?

Thanks,

Bill

[Tom Ogilvy]

You can try this rather than copying the whole column.

If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Branchname
Call FindtheCol(ColName) finds a spefiic column
NColl = ActiveCell.Column
set rng = Union(Columns(2), Columns(NColl))
set rng1 = ActivesheetAutofilter.Range.Specialcells(xlVisible)
set rng2 = Intersect(rng2.EntireRow,rng1)
rng2.copy

--
Regards,
Tom Ogilvy



"Bill" <bill@bpiconsulting.com> wrote in message
news:go7df.7991$AS6.525@newsread3.news.atl.earthlink.net...
> Hello,
> I have a table that I filter and then copy to a new page. The problem is
> that the table is only about 20 rows long when I filter it. But, when I
> copy it to a new worksheet, the workbook becomes almost 3.5 MB in size.
It
> is like the workbook thinks there is something all the way down to row
> 65,000. The code I use is:
>
> If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
> Selection.AutoFilter Field:=1, Criteria1:=Branchname
> Call FindtheCol(ColName) finds a spefiic column
> NColl = ActiveCell.Column
> Union(Columns(2), Columns(NColl)).Copy
>
> Any ideas on how to avoid this?
>
> Thanks,
>
> Bill
>
>