Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet
 

Hi guys,

I'm working now on a piece of code that is basically choosing criteria in
column A and copying displayed info next to it (column A excluded) to
another place.

(...)
Selection.AutoFilter Field:=1, Criteria1:="myCriteria"
Range("B...").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
(...)

I have 3 questions related to this:

a) how to tell the code that if there are no rows fulfilling the criteria,
it should skip this part and go further?
b) how to specify the row number in "Range("B...").Select" (it's obviously
varying depending on criteria)?
- I could perhaps start in A2 instead of B<volatile_row - that could
be one of the solutions, both for point b) and c)
c) how to avoid the situation where there is only one line related to
criteria and using "Selection.End(xlDown)" will go until 65536?

I'll continue to look for a solution by myself, but if you know the answer,
thanks to let me know...

Thanks and cheers,
Mark

Selection.AutoFilter Field / Criteria = criteria sometimesnon-existing on worksheet
 

Maybe you can incorporate something from this:

Option Explicit
Sub testme()

Dim rngF As Range
Dim rngV As Range
Dim DestCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
'remove any existing filters
.AutoFilterMode = False

.Range("a1").CurrentRegion.AutoFilter field:=1, Criteria1:="myCriteria"

Set rngF = .AutoFilter.Range
If rngF.Columns.Count = 2 Then
MsgBox "Please filter on more than one column"
Exit Sub
End If
If rngF.Columns(1).Cells.SpecialCells(xlCellTypeVisib le) _
.Cells.Count = 1 Then
MsgBox "Only the header is shown"
Exit Sub
End If
End With

With rngF
'ignore the header from the count and come down one row
'and one column to the right
Set rngV = .Resize(.Rows.Count - 1, .Columns.Count - 1).Offset(1, 1) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

With Worksheets("sheet2")
Set DestCell = .Range("a1") 'where should this be???
End With

rngV.Copy _
Destination:=DestCell

End Sub

markx wrote:

Hi guys,

I'm working now on a piece of code that is basically choosing criteria in
column A and copying displayed info next to it (column A excluded) to
another place.

(...)
Selection.AutoFilter Field:=1, Criteria1:="myCriteria"
Range("B...").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
(...)

I have 3 questions related to this:

a) how to tell the code that if there are no rows fulfilling the criteria,
it should skip this part and go further?
b) how to specify the row number in "Range("B...").Select" (it's obviously
varying depending on criteria)?
- I could perhaps start in A2 instead of B<volatile_row - that could
be one of the solutions, both for point b) and c)
c) how to avoid the situation where there is only one line related to
criteria and using "Selection.End(xlDown)" will go until 65536?

I'll continue to look for a solution by myself, but if you know the answer,
thanks to let me know...

Thanks and cheers,
Mark

--

Dave Peterson