Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=DMIN(database,field,criteria) question about criteria | Excel Discussion (Misc queries) | |||
Autofilter criteria not yet known | Excel Programming | |||
AutoFilter criteria | Excel Programming | |||
Selection.AutoFilter Field:=1 | Excel Programming | |||
AutoFilter Criteria VBA | Excel Programming |