Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGE NAMES IN MACRO WITH ADVANCED FILTERING
Advanced Filtering is a great tool while using Macro's.
So far it has been working perfectly with ranges consisting of Cell names like the example below: Sheets("2 Air").Select Application.Goto Reference:="NO2" ActiveCell.FormulaR1C1 = "NO" Range("A3:X503").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "A531:X532"), CopyToRange:=Range("A536:X636"), Unique:=False ActiveWindow.SmallScroll Down:=11 ActiveWindow.ScrollWorkbookTabs Position:=xlLast However I need to make to the ranges variable as number of lines above will vary. So I defined the ranges: A3:X503 as Lines1 A531:X532 as CRIT1 A536:X736 as ADVFILTRES1 I changed the macro as follows: Sheets("2 Air").Select Application.Goto Reference:="NO2" ActiveCell.FormulaR1C1 = "NO" Range("Lines1").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "CRIT1"), CopyToRange:=Range("ADVFILTRES1"), Unique:=False ActiveWindow.SmallScroll Down:=11 ActiveWindow.ScrollWorkbookTabs Position:=xlLast RESULT the Macro stops and highlights the AdvancedFilter line. Range(lines1).xxx or Lines1.xxx or "Lines1".xxx did not improve anything. Can somebody tell me what I am doing wrong? Thank you very much for you help. I really appreciate this Discussion Group. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGE NAMES IN MACRO WITH ADVANCED FILTERING
It always scares me when I try to put too much stuff on a single worksheet.
In a case like this, I'd put the criteria range on a different sheet and copy the results to a different sheet, too. I find that keeping the data separate makes it easier to use. For instance, if I wanted to find the last row of a table full of data, I could just pick out a column that always has data and go from the bottom up to find the last used row. But if I have stuff under my table, then I can't do this--and determining the last row could be a pain. In your code, I didn't know where range("no2") was located. So I used the 5th column of my database. I would expect that this isn't close! Anyway, if you want to try: Option Explicit Sub testme() Dim CurWks As Worksheet Dim LinesRng As Range Dim CritRng As Range Dim ResRng As Range Set CurWks = Worksheets("Sheet1") With CurWks 'here's where I used the last row in column A Set LinesRng = .Range("a3:x" & .Cells(.Rows.Count, "A").End(xlUp).Row) End With Set CritRng = Worksheets.Add.Range("a1") Set ResRng = Worksheets.Add.Range("a1") 'copy first row (headers) to the criteria range LinesRng.Rows(1).Copy _ Destination:=CritRng 'put "NO" in whatever field is required 'I used the 5th field (4 to the right of column A and one row down) 'see a warning below 'CritRng.Offset(1, 4).Value = "no" CritRng.Offset(1, 4).Value = "=" & Chr(34) & "=no" & Chr(34) 'resize that criteria range to match the Lines range 'but only 2 rows (for now???) Set CritRng = CritRng.Resize(2, LinesRng.Columns.Count) LinesRng.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=CritRng, _ CopyToRange:=ResRng, _ Unique:=False Application.DisplayAlerts = False CritRng.Parent.Delete Application.DisplayAlerts = True MsgBox "Output is on: " & ResRng.Parent.Name End Sub You may not have noticed, but if you have "no", "no way", "north" or "nothing" in that field, then they all come across in your advanced filter when you use: CritRng.Offset(1, 4).Value = "no" If you only want the "no"--not stuff that starts with "no", then you can use this line: CritRng.Offset(1, 4).Value = "=" & Chr(34) & "=no" & Chr(34) If you select that cell and look at the formula bar, you'll see: ="=no" (not just plain old No) Ulrik Loves Horses wrote: Advanced Filtering is a great tool while using Macro's. So far it has been working perfectly with ranges consisting of Cell names like the example below: Sheets("2 Air").Select Application.Goto Reference:="NO2" ActiveCell.FormulaR1C1 = "NO" Range("A3:X503").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "A531:X532"), CopyToRange:=Range("A536:X636"), Unique:=False ActiveWindow.SmallScroll Down:=11 ActiveWindow.ScrollWorkbookTabs Position:=xlLast However I need to make to the ranges variable as number of lines above will vary. So I defined the ranges: A3:X503 as Lines1 A531:X532 as CRIT1 A536:X736 as ADVFILTRES1 I changed the macro as follows: Sheets("2 Air").Select Application.Goto Reference:="NO2" ActiveCell.FormulaR1C1 = "NO" Range("Lines1").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _ "CRIT1"), CopyToRange:=Range("ADVFILTRES1"), Unique:=False ActiveWindow.SmallScroll Down:=11 ActiveWindow.ScrollWorkbookTabs Position:=xlLast RESULT the Macro stops and highlights the AdvancedFilter line. Range(lines1).xxx or Lines1.xxx or "Lines1".xxx did not improve anything. Can somebody tell me what I am doing wrong? Thank you very much for you help. I really appreciate this Discussion Group. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filtering | Excel Discussion (Misc queries) | |||
Advanced filtering macro | Excel Worksheet Functions | |||
Advanced Filtering by Date (or Macro) Help | Excel Worksheet Functions | |||
Advanced Filtering Extract Range Missing,etc | Excel Worksheet Functions | |||
Macro to perform Advanced Filtering | Excel Programming |