Problems selecting correct data after filter is in place

**DarthWire** · 01-02-2013, 09:08 AM

I have an issue where as after a filter is in place the number of rows could vary between 1 and about 50. The code I wrote below was to try to get around the problem of selecting the entire worksheet when the filter only returns one row but doesn't work. Can anyone help me find out where I have gone wrong?

    Sheets(CStr(Workbooks("#Lateness One Pilot V3.1.xls").Sheets("Front Sheet").Range("E14"))).Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$M$75").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria1:="=" & Sheets("Temp Dump").Range("A1").Value
        
    Rows("2:2").Select
    chkrow = Sheets(CStr(Workbooks("#Lateness One Pilot V3.1.xls").Sheets("Front Sheet").Range("E14"))).Range("A65536").End(xlUp).Row
        If Worksheets(CStr(Workbooks("#Lateness One Pilot V3.1.xls").Sheets("Front Sheet").Range("E14"))).Cells(chkrow + 1, "A") > "" Then
        Range(Selection, Selection.End(xlDown)).Select
        End If
    Selection.Copy

**JosephP** · 01-02-2013, 09:27 AM

perhaps

   Dim ws As Worksheet
    Set ws = Sheets(CStr(Workbooks("#Lateness One Pilot V3.1.xls").Sheets("Front Sheet").Range("E14").Value))
    With ws.Range("$A$1:$M$75")
      .AutoFilter
      .AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria1:="=" & Sheets("Temp Dump").Range("A1").Value
      If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
         .Resize(.Rows.Count - 1).Offset(1).Copy
      End If
   End With