RANGE NAMES IN MACRO WITH ADVANCED FILTERING

[Guest]

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.

 

[Don Guillett]

Sub fi() tested
Range("A3:c9").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("e3:e4"), CopyToRange:=Range("h3:j9")
End Sub

Then named the ranges
This worked from ANYWHERE in the workbook, as is, withOUT selections or
goto.

Sub fii()'changed to defined names
Range("NO2") = "NO"
Range("srcrng").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("crrng"), CopyToRange:=Range("dstrng")
End Sub

 

[Dave Peterson]

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)