Huge Worksheet

  • Thread starter Thread starter Bill
  • Start date Start date
B

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
 
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
 
Back
Top