Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing data returned from an autofilter
Hi,
I am working on code that takes my list, filters first on column a, changes the data in column a from one thing to another depending on what the autofilter returns, puts on another filter in the same column with different criteria, changes those, and so on. My problem is that if there is a zero count on the data returned, the text is copied across the entire spreadsheet (a1:iv1) (don't know if this is relevant or not, but when the zero count is returned, i don't see any of the spreadsheet except for my headers - the rest of it is that nether region beyond row 65536)... I've exhausted my brain trying to figure this out. Below is a snippet of the code that is causing me a problem: Selection.AutoFilter Field:=1, Criteria1:="=E*", Operator:=xlOr, Criteria2:="=D*" Selection.AutoFilter Field:=2, Criteria1:="S", Operator:=xlOr, Criteria2:="E" Range(ActiveCell, ActiveCell.End(xlDown)).SpecialCells (xlCellTypeVisible).Value = "UNDER INVESTIGATION" ActiveSheet.ShowAllData Range("A1").Offset(1, 0).Select Does anybody have any suggestions? I hope I've explained this clearly... Thanks, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing data returned from an autofilter
Dim rng as Range, rng1 as Range
set rng = Range(ActiveCell, ActiveCell.End(xlDown)).SpecialCells(xlCellTypeVis ible).Value set rng1 = rng.Areas(rng.Areas.count) if rng1.rows(rng1.rows.count).Row < 65536 rng.Value = "UNDER INVESTIGATION" or another might be Dim rng as Range, rng1 as Range set rng1 = Nothing set rng = ActiveSheet.Autofilter.Range set rng = rng.offset(1,0).Resize(rng.rows.count - 1) set rng = intersect(rng,Activecell.EntireColumn) on error resume next set rng1 = rng.SpecialCells(xlVisible) On error goto 0 if not rng1 is nothing then rng1.Value = "UNDER INVESTIGATION" End if -- Regards, Tom Ogilvy "ChrisBat" wrote in message ... Hi, I am working on code that takes my list, filters first on column a, changes the data in column a from one thing to another depending on what the autofilter returns, puts on another filter in the same column with different criteria, changes those, and so on. My problem is that if there is a zero count on the data returned, the text is copied across the entire spreadsheet (a1:iv1) (don't know if this is relevant or not, but when the zero count is returned, i don't see any of the spreadsheet except for my headers - the rest of it is that nether region beyond row 65536)... I've exhausted my brain trying to figure this out. Below is a snippet of the code that is causing me a problem: Selection.AutoFilter Field:=1, Criteria1:="=E*", Operator:=xlOr, Criteria2:="=D*" Selection.AutoFilter Field:=2, Criteria1:="S", Operator:=xlOr, Criteria2:="E" Range(ActiveCell, ActiveCell.End(xlDown)).SpecialCells (xlCellTypeVisible).Value = "UNDER INVESTIGATION" ActiveSheet.ShowAllData Range("A1").Offset(1, 0).Select Does anybody have any suggestions? I hope I've explained this clearly... Thanks, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use autofilter in excel with formula reference changing | Excel Worksheet Functions | |||
How do I keep my chart from changing when excel has autofilter? | Charts and Charting in Excel | |||
CHANGING POSITION OF AUTOFILTER LIST | Excel Discussion (Misc queries) | |||
#value returned instead of data | Excel Worksheet Functions | |||
Finding row count and filtered rows returned by Autofilter | Excel Programming |