Selecting autofiltered cells

[Annita]

Hi,
I am trying to create a macro in Excel 97 that will select some
autofiltered cells from one sheet , copy and paste them to another
sheet. The code that I am using is:

With Sheets("Initial Test Store Data").AutoFilter.Range
On Error Resume Next
Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1)
..SpecialCells (xlCellTypeVisible)
Set Year_Week_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Retail_Outlet_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Product_Types = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

But when running the program VB does nothing at this point. Can you
help me?

Thank you

 

[Tom Ogilvy]

You set 4 variables to the same range (visible cells in the autofilter
except the header row, first column). You never do a copy

Sum_Order_Amt.Entirerow.copy _
Destination:=worksheets("Sheet2").Range("A1")



With Sheets("Initial Test Store Data").AutoFilter.Range
On Error Resume Next
Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1)
..SpecialCells (xlCellTypeVisible)
Set Year_Week_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Retail_Outlet_Number = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
Set Product_Types = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

 

[Tushar Mehta]

It would appear that you are setting four object variables to refer to
the same range. But then, I don't see any attempt to copy anything.
Maybe, you want something like:

With Sheets("Initial Test Store Data").AutoFilter.Range
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("sheet2").Range("a1")
End With
Worksheets("sheet2").Range("a1").EntireRow.Delete

Also, unless you have a very specific reason why you need to avoid
errors with 'On Error Resume Next' don't use such a statement. All
that it does is make debugging difficult, if not impossible.

There are a few subtle problems with your code.
* Rows.Count returns a count of only the rows in the first area of a
non-contiguous range.
* What does it mean to resize a range that contains multiple areas?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tom Ogilvy]

There are a few subtle problems with your code.

* Rows.Count returns a count of only the rows in the first area of a
non-contiguous range.

but if .Rows.count is qualified by

With Sheets("Initial Test Store Data").AutoFilter.Range

as it appears to be in (for example)

Set Sum_Order_Amt = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells (xlCellTypeVisible)

Then it should be a contiguous single area and appropriately used here to
exclude the header from the rest of the range.

 

[Tushar Mehta]

Hi Tom,

For some reason, in interpreting the post, I mentally transformed

..Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells (xlCellTypeVisible)

into

..SpecialCells (xlCellTypeVisible).Offset(1, 0) _
.Resize(.Rows.Count - 1, 1)

and proceeded with that as the starting point.

Duh!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions