VBA autofilter question

[McCarls]

For a project I'm doing I have a userform that autofilters a range of products according to values of particular comboboxes within the form. Once the autofilter is complete, the resulting rows are copied to a separate worksheet, "temp3".

Problem: For the most part this code works well, however, if the autofilter does not yield any results i.e. no products match the user specs, the entire product list is copied over instead of simply copying no cells.

Here is the code:

Private Sub ComboBox1_Change()
ComboBox2.Value = "Any"
With ActiveWorkbook.Worksheets("ODU")
.Range("A1:I1").AutoFilter
If ComboBox1 = "Any" Then
.Range("A1:I1").AutoFilter field:=5, Operator:=xlAnd
Else
.Range("A1:I1").AutoFilter field:=5, Criteria1:=CInt(ComboBox1), Operator:=xlAnd
End If


If ComboBox2 = "Any" Then
.Range("A1:I1").AutoFilter field:=8, Operator:=xlAnd
Else
.Range("A1:I1").AutoFilter field:=8, Criteria1:=CInt(ComboBox2), Operator:=xlAnd
End If

If CheckBox1 Then
.Range("A1:I1").AutoFilter field:=6, Criteria1:="TRUE"
Else
.Range("A1:I1").AutoFilter field:=6, Criteria1:="FALSE"
End If
   Worksheets("temp2").Cells.Clear
   Set Rng = .AutoFilter.Range
   Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Copy _
     Destination:=Worksheets("temp2").Range("A1")
End With

End Sub

 

[Hashiru]

Hi
check the last three lines of code.

Points noted:
You set the filtered range and then offset it by 1,0. This must be copying things out of the filtered range.
check you resize argument, what is the value for the column argument, i thing it will defalt to o meaning the same as it was.

Hope this help

 

[Hashiru]

Can you make up fictitous data set to help resolve these problems. Thanks

 

[McCarls]

I found a solution to the problem. Basically I added a .SpecialCells(xlCellTypeVisible) so it only grabs the visible cells from the range

On Error Resume Next
Worksheets("temp2").Cells.Clear   
Set Rng = .AutoFilter.Range
Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy _
     Destination:=Worksheets("temp2").Range("A1")