Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Advanced Filter - error message

Hi there,

Can anyone tell me what the following message means and how to stop it please

'The extract range has a missing or illegal field name'

In simple language please - not even sure what extract range and field name
are.

The filter works. It puts the data expected into the cell I tell it to but
an error message pops up afterwards.

I would be grateful for any answers.

Cheers
Mifty
--
Mifty


Post your answer or comment

comments powered by Disqus
When using Advanced Filters for Rail “Day Validity Profiles” (DVP) i.e. The
days of the week that a Train is Timetabled (shown below), I have asked for a
filter showing only certain DVPs... It is also showing others.

Example:
Data including all DVPs are filtered by only those that include Mondays (MO,
TX, WX, ThX, FX, MTO, MWO, MThO, MFO, TWX, TThX, MWX, TFX, WThX, WFX, ThFX).
The results (for no reason I can fathom) include “MX”.

Does anyone know if there are known issues with this, or if there are, if
there are known solutions?

Standard DVPs
MO Monday Only
TO Tuesday Only
WO Wednesday Only
ThO Thursday Only
FO Friday Only
MX Monday Excepted
TX Tuesday Excepted
WX Wednesday Excepted
ThX Thursday Excepted
FX Friday Excepted
MTO Monday and Tuesday Only
MWO Monday and Wednesday Only
MThO Monday and Thursday Only
MFO Monday and Friday Only
TWO Tuesday and Wednesday Only
TThO Tuesday and Thursday Only
TFO Tuesday and Friday Only
WThO Wednesday and Thursday Only
WFO Wednesday and Friday Only
ThFO Thursday and Friday Only
MTX Monday and Tuesday Excepted
MThX Monday and Thursday Excepted
MFX Monday and Friday Excepted
TWX Tuesday and Wednesday Excepted
TThX Tuesday and Thursday Excepted
MWX Monday and Wednesday Excepted
TFX Tuesday and Friday Excepted
WThX Wednesday and Thursday Excepted
WFX Wednesday and Friday Excepted
ThFX Thursday and Friday Excepted

I keep encountering the following message whilst trying to filter data via macro.

Here is an example of the offending code - I'm very new to VBA so please assume I have little/no knowledge. Many thanks in advance

Sometimes this code does execute but the majority of the time it doesn't - and I certainly wouldn't know why!

I've tried fully qualifying the range for the filter criteria but this has made no difference. Any thoughts?

Also, it appears that after running other advanced filters (from early code) the worksheet is getting stuck with some rows as hidden (or row numbers highlighted blue). The code at the top of the sub-routine don't seem to do anything about this.


	VB:
	
Sheets("Data Input & Format").Select 
If ActiveSheet.AutoFilterMode = True Then 
     
    ActiveSheet.AutoFilterMode = False 
     
Else 
     
End If 
 
If ActiveSheet.FilterMode = True Then 
    ActiveSheet.FilterMode = False 
     
Else 
     
End If 
 
Range("AH7:AM50000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _ 
:=ThisWorkbook.Sheets("Data Input Support").Range("R6:R7"), Unique:=False 
 
Dim rng3 As Range 
Set rng3 = Worksheets("Data Input & Format").Columns("AH:AM") 
Set rng3 = rng3.Resize(50000, 1).Offset(7, 0) 
Set rng3 = rng3.Resize(, 6).SpecialCells(xlCellTypeVisible) 
 
rng3.Copy 
 
Sheets("Record Errors").Select 
Range("H6").Select 
If Range("H6").FormulaR1C1 = "" Then 
    ActiveSheet.Paste 
Else 
    Range("H6").Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(1, 0).Select 
    ActiveSheet.Paste 
End If 
 
 
 
Sheets("Data Input & Format").Select 
 
 
ActiveSheet.ShowAllData 
 
 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


G'day,

Does anybody know how to tell a macro to return an "Error" message if an Advanced Filter macro search returns no results? I realise that an "If" statement is required, but I have no idea of where or when to put this statement! The VBA code is set out below - how can I tell the macros (all three) to display an error message if no result is found (ie name is misspelt)?

	VB:
	
 SelectLanguage() 
    Application.Goto Reference:="CriteriaValues" 
    Selection.Clear 
    Application.Goto Reference:="Language" 
    ActiveCell.FormulaR1C1 = InputBox("Enter the Language") 
    Range("A15").Select 
    Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ 
    Range("Criteria"), Unique:=False 
     
End Sub 
Sub SelectClient() 
    Application.Goto Reference:="CriteriaValues" 
    Selection.Clear 
    Application.Goto Reference:="Last_Name" 
    ActiveCell.FormulaR1C1 = InputBox("Enter the client's last name") 
    Range("A15").Select 
    Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ 
    Range("Criteria"), Unique:=False 
     
End Sub 
Sub ShowAllClients() 
    Application.Goto Reference:="CriteriaValues" 
    Selection.Clear 
    Range("A15").Select 
    Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ 
    Range("Criteria"), Unique:=False 
    MsgBox "Macros created by Christian Baldock" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all,

I wrote a macro in my scrap file which includs and advanced filter funtion. It was working perfectly until I copied and pasted the code to the new file that I created. When I try to execute the code again, it shows me the "Application-defined or object-defined error".

After using breakpoint to find where does the error occur. I found out it happens in the advanced filter code.

Please find below part of the code as the last part is where the error occur.


	VB:
	
 
Sub Advancedfilter() 
    Dim CCcount As Integer 
    Dim TotalCCcount As Integer 
    Dim CurrentRow As Integer 
    Dim ClassCount As Integer 
    Dim CategoryCounter As Integer 
    Dim CategoryCount As Integer 
    Dim SizeRangeCount As Integer 
    Dim AllocatorCount As Integer 
    Dim i As Integer 
    Dim j As Integer 
    ClassCount = Sheets("Data Range").Range("A300").End(xlUp).Row - 1 
    CategoryCount = Sheets("Data Range").Range("C300").End(xlUp).Row - 1 
    SizeRangeCount = Sheets("Data Range").Range("E300").End(xlUp).Row - 1 
    AllocatorCount = Sheets("Data Range").Range("B300").End(xlUp).Row - 1 
     
    i = 1 
    j = 2 
     
     'Setting for the first filter
    Range("A24").Select 
     
    CategoryCount = Sheets("Data Range").Range("C300").End(xlUp).Row - 1 
     
    For CategoryCounter = 1 To CategoryCount 
         
         
        CurrentRow = ActiveCell.Row 
         
         
         'Copy and paste headers to for the next category
        With Worksheets("Data Table").Range("DataTableHeadersRow").Copy 
            ActiveCell.PasteSpecial 
        End With 
         
         'Filter data to Advanced Filter Tab base on criteria in size average tab
        Sheets("Data Table").Rows("24:1300").Advancedfilter Action:=xlFilterCopy, _ 
        CriteriaRange:=Sheets("Data Range").Range("M" & i & ":O" & j & ""), CopyToRange:=Rows( _ 
        CurrentRow), Unique:=True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Can anyone tell me what is my syntax error?

Thank you in advance!

Haijun

Hi guys,
Can anyone help me with the Advanced Filter option in Excel? The applicable worksheet is attached to this thread.

I need to use the Advanced Filter tool to allow the user to filter (in-place) the Guests worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.

To automate the operation of the advanced filter tool, I need three macros. "SelectRoom" and "SelectGuest" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllGuests" should clear the criteria row and dispay all clients in the database.

The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllGuests" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Guests worksheet!

Help would be greatly appreciated!

Hi guys,
Can anyone help me with the Advanced Filter option in Excel? The applicable worksheet is attached to this thread.

I need to use the Advanced Filter tool to allow the user to filter (in-place) the Clients worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.

To automate the operation of the advanced filter tool, I need three macros. "SelectClient" and "SelectLanguage" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllClients" should clear the criteria row and dispay all clients in the database.

The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllClients" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Clients worksheet!

Help would be greatly appreciated!

Christian

HI, there.. Hope you are fine !!!!

Is there any way to use Advanced Filter placing the result (the filtered data) in another sheet ???

I have tried but with no success... An error message is displayed (something like "It's not possible copy filtered data from a sheet to another")...
What do I have to do to get it ???

Thanks a lot !!!

Marcos.

Hi,
I am having trouble with the advanced filter. I have typed in the code below and keep getting the error message - instruction or object defined error.
This is strange as the code is copied from a book. I have consulted another book and it is identical. Also if I manually set the advanced filter everything works fine. What am I doing wrong??


	VB:
	
 selected() 
    Dim objBL As Worksheet 
    Set objBL = ThisWorkbook.Worksheets("Array") 
    objBL.Range("b1:c4").AdvancedFilter _ 
    xlFilterCopy, _ 
    Range("I1:I2"), _ 
    Range("b15") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance
John

I have an excel workbook that contains one worksheet named "Data". I need to filter the data in Column A of the "Data" worksheet to contain only unique records automatically via VBA. The data in column A changes daily and may contain more or fewer records than the previous day. My idea was to find the last row used in column A, build a string of the range "A2:last row used", then create the range and use the advanced filter however I keep getting the Method 'Range' of object _Global' failed error. Code below...


	VB:
	
 SORTWITHUNIQUE() 
     'This sub will set a range from A2 to the last cell used in column A then filter the range
     'returning only unique records
    Dim LROW As Integer 
    Dim CRANGE As Range 
    Dim RSTRG As String 
    RSTRG = "A2:A" & "" & Worksheets("Data").Range("A65536").End(xlUp).Row & "" 
    Set CRANGE = Worksheets("Data").Range(RSTRG) 
    Range(CRANGE).AdvancedFilter Action:=xlFilterCopy, Unique:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What do I need to do to get the advanced filter to work? Or is there a better way?

I have 2 sheets in a workbook.
In Sheet 2, I set up 3 headings and entered 2 criteria under 1st heading, and 1 criteria under the remaining headings. It looks like so:
Date: Cell or Station ID Total APU %
>7/30/2002 Test >0.00

I have an Excel file that is storing machine delay and efficency inputs for multiple machines and on a daily basis. I want to filter (I don't know how to query)by date and machine ID. I have set up an advance filter on the datasheet. I can successfully get the headers, but I can't get any data. Here's what the criteria look like for this filter:

Date: Cell or Station ID Total APU %
0.00
>7/1/02 test

WHen I run the filter, I opted to place the results in another location within the sheet. I get just a listing of all my headers. I also get the following error message:

"The extract range has a missing or illegal file name"

How do I get this filter to work? Ideally, I'd like the filter results to copy to another sheet so that I may manipulate data. The filter will not allow me to do this saying that "you can only copy filtered data on the active sheet".

Thanks for your assist!
dab1477

I'm trying to automate an Advanced Filter so that when the user enters text into a range of cells the filter is applied automatically. I'd like the Filter Criteria's 'default' to include the "*" so that the user does not have to know to type it each time (the data to be filtered is not 'Data Validated' and may contain variations of what they are filtering for). I'm trying to do this in 4 steps...
1) Have the user type criteria into any cell in an 'input range'.
2) Concatenate "*" with the criteria the user inputs.
3) Remove the "*" from the cells that the user has not entered crieria into (otherwise the filter returns nothing because the cells with just "*" are also filtered on.
4) Apply the filter using a macro executed by a Worksheet_Change sub.

When I enter criteria anywhere in row 2 the filter applies correctly, but hides ALL rows in the list (returns no results).
If I remove the formulas from cells A10:D10 (the Advanced Filter's Criteria Range) and simply type the same text that the formulas would result in, the filter works perfectly. Can anyone tell me what I am doing wrong?

(FYI -Rows 6 and 10 are not hyperlinks... the HTML utility made them look like hyperlinks).

******** ******************** ************************************************************************>Microsoft Excel - test2.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA6B6C6D6A10B10C10D10=
ABCDEF1NameDateAddressAccount**2Smith****User's*Criteria*Input*Range*(No*formulas)3******4******5NameDateAddressAccount**6*Smith****Concatenate*"*"*with*user's*Criteria*above.***Formula*example*=Concatenate("*",A2)7******8******9NameDateAddressAccount**10*Smith*
*
*
*Remove*"*"*from*cells*with*no*criteria*provided*by*user*(this*is*the*final*Criteria*Range*11*****used*for*Advanced*Filter*Criteria).***Formula*example*=IF(LEN(A6)>1,A6,"")12******13NameDateAddressAccount**14Mary*Smith12/31/2002Oak*Street23498**15M.*Smith12/10/2002Oak*Street23498*List*Range16Robert*Spoon12/2/200243rd*Avenue23091*to*be17Rob*Spoon12/2/200243rd*Avenue23091*filtered*by18Sue*White9/14/2002Elliot*Road23876*Advanced*Filter19White,*Sue9/14/2002Elliot*Road23876**20Steve*Mitchel9/20/2002Wabash*Road35698**21R.*W.*Spoon12/2/200243rd*Avenue23091**Sheet1*
[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[ This Message was edited by: RogerC on 2002-12-28 23:56 ]

Hello, I cannot seem to get a simple advanced filter to work using VBA. It works fine when I perform the task manually on the same data range and criteria, but when I use the code below I get the message: " Run time error '1004' The extract range has a missing or illegal field name". My code is below:
Sheet12.Activate
Sheet12.Range("A1:R20000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("t1:t4"), CopyToRange:=Range("AA1:AR1"), Unique:=False
ActiveSheet.Range("AA1").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Any help would be much appreciated.

Thx, Wratpack

Hi,
I require to use advanced filter options for 3 times opening different
files. When I use the advanced filter for first time it works, but when
I open the second file and use the advanced filter, select the range,
select the criteria range and click on OK. I get an error as "Memory
could not be read".(The actual message screen shot has been attached).

Could anybody elighten me the cause of this error and the remedy for
the same.

Thans in advance for your help.

Regards
Anand

+-------------------------------------------------------------------+
|Filename: error-Advanced filter.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4644 |
+-------------------------------------------------------------------+

--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=533347

Hey guys I have spent the last 30 minutes searching and found answers too simple and too complicated. I think what I need is simple code, but I don't know how to write it:

I have a list of Window sizes and types in Range B4:B:43. The descriptions repeat because in Column K I am listing a location for each window in a house.

Example:
B K
3050 SH 1/1 Dining Room
3050 SH 1/1 Kitchen
2030 Fixed Foyer
2030 Fixed Living Room

In the Same sheet Starting on B:45 I want a list of only Unique Window Types:

B:45 and Down:
3050 SH 1/1
2030 Fixed

I'd like this to automatically appear after populating the first list. I used an advanced filter the first time and it worked, but it is not reliable and sometimes returns duplicate values or give me an error message. Plus once again having it happen automatically as the list will change each time I access the sheet would be great.

Thanks,
Nick

Hi, having trouble using the copy to another location option within advanced
filtering. i am trying to extract multiple rows that contain the company
name specified as my criteria. when i filter it in place it works but when i
try and copy it to another worksheet i get the error message " you can only
copy filtered data to the active sheet" Any one got any ideas what i am doing
wrong?? Thanks

Hi -

I am using the below code to fun an advanced filter and copy the data to a new destination - when I do this manually the advanced filter works - however when running the macro i get an error message - "Run-time error '1004' the extract range has a mssing or illegal field name." Can someone please help me fix this error.

Private
Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 3 Or _
Target.Row = 2 And Target.Column = 3 Or _
Target.Row = 3 And Target.Column = 3 Or _
Target.Row = 4 And Target.Column = 3 Or _
Target.Row = 5 And Target.Column = 3 Or _
Target.Row = 6 And Target.Column = 3 Then
    Sheets("Sheet2").Select
     Sheets("Active Data").Range("A:K") _
    .AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=Sheets("Active Data").Range("M1:R2"), _
      CopyToRange:=Range("A1:K1"), Unique:=False

End If

End Sub
Thanks,

Hi,
I require to use advanced filter options for 3 times opening different files. When I use the advanced filter for first time it works, but when I open the second file and use the advanced filter, select the range, select the criteria range and click on OK. I get an error as "Memory could not be read".(The actual message screen shot has been attached).

Could anybody elighten me the cause of this error and the remedy for the same.

Thans in advance for your help.

Regards
Anand

Hey!
I have made a macro with advanced filter. When I share this workbook i get a
message:
"advanced filter in range mode failed: error 1004" (something like that).
The code is:
Sub Idag()
'
Dim omrĂĄde As Range
Dim kriterie As Range
Dim dato As Range

Set omrĂĄde = Range("Database")
Set kriterie = Range("kriterie")
Set dato = Range("A7")

Application.ScreenUpdating = False
Application.Goto Reference:=("Database")
omrĂĄde.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
kriterie, Unique:=False
dato.Select

End Sub

Can anybody help!
TD

hi everyone... long time listener first time caller here from australia...

so basically my problem is massive.

for a senior finals assignment at school i have to use the advance filter function in a macro to create a semi automated filtration of a client list, problem is i suck at recording and coding macros that have more than one procedure.. please help

criteria is as follows

You are required to use the Advanced Filter tool on this worksheet to allow
users to filter (in place) the Clients worksheet while providing the following database
statistics: total, average, maximum and minimum revenue per client, and the total number of clients/tours. That is, users should be able to enter any criteria value below the Clients database and invoke the advanced filter tool to show only those records, and the summary statistics for those records, that satisfy the given criteria. Note that average calculation should have value of zero if no records satisfy the given criteria.
You must include three macros in your finished Clients worksheet to automate
operation of the filter tool. The first two macros are entitled 'SelectClient’ and
'SelectLanguage'. Once invoked, these macros should prompt the user to enter a value through an input box in order to filter the data range in place according to a client's last name or language tour, as appropriate. A third macro, called 'ShowAllClients’, should clear the criteria row and display all clients in the database. The first two macros should include an error message (e.g. “Please re-run the macro!”) to prompt the user to rerun a macro if no clients satisfied the criteria while the 'ShowAllClients' macro should include a message box statement at the end to display a short message giving credit to the macro authors (e.g. “This macro was created by <insert your name here>!”). Finally, assign and name one button to each of these macros (three buttons in total) on the Clients worksheet

if anyone could either wrote the coding i need for the macros or put them into the attached workbook and reattach it it would be most appreciated as i am going out of my mind trying to figure this out, and if i see it done once for one of my several work books i should be able to implement it into my others..

thanks in advance,
ryan

Folks,

I have an excel file which is used as a database of contact
information. There are several macro buttons which the user can use
to show the whole list or to filter the list. The VBA code uses the
AdvancedFilter command...

Range("sysFilterList").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("sysCriteria"), Unique:=False

The file operates correctly on many machines in the office, most of
whom are running Windows 2000 & Excel 2000.

A colleague who runs Windows XP and Excel 2003 cannot use the file as
the procedure hangs at this line of code. On escaping out of the code
I get the error message "AdvancedFilter method of range class failed".

Can anyone out there guess why this file isn't working on my
colleague's machine?

Thanks in advance

Stuart

Hello friends,

Description Of My Macro:

I have made a macro which use the data from excel sheet and make documents of MS word in a folder. e.g., "C:PTA BTS Summary" & (Sheets(1).Cells(r, 2).Value) & "" & (Sheets(1).Cells(r, 2).Value) & ".doc" ("r" is a row variable which is incremented in a loop)This is the destination where the word documents are created from excel data. Excel has information of almost 616 BTS sites alongwith its specific data. My macro uses the information from excel and make MS word documents of each BTS site in a folder.

My Problem:

My problem is that, when i run my macro, then if there are some documents alredy present in the folder, it generates an error message that file already exists and my macro stops. I want to pop an application/msg box that if my macro encounters this type of error that file already exists, it should pop an application/msg box which must be like this:

The msg box must show that "File alredy exists. Do you want to delete the file?" And it must contain 4 options, Yes, No, Yes to all, No to all.
On clicking on any one of these 4 options by the user, my macro should do the following function:

1. Yes: delete the file and go to next file.
2. No: Do not delete the file and go to next file.
3. Yes to all: Delete the file and if next time this error exists, do the same function as done with the previous file.
4. No to all: do not delete the file and if next time this error exists, do the same function as done with the previous file.

After clicking any of the above 4 options, my macro should do what i want to do. What should i do..??

Any kind of help is warmly welcomed. Thanking in advance.

Best Regards,
Haider Sultan

I have created an advanced filter using the code you provided as a base and changing it as follows.

Ozgrid post1


	VB:
	
 AdvancedFilterToOtherSheet() 
    Sheet23.Range("FilteredMonsters").Clear 
    Range("AllMonsters").AdvancedFilter xlFilterCopy, Range("FilterMonstersCriteria"), Sheet23.Range("FilteredMonsters") 
    Range("AllFilteredMon").WrapText = False 
    Sheet1.Range("G2").Value = 1 
    Sheet1.Range("G3").Value = 1 
     
    On Error Resume Next 
     
    If Application.WorksheetFunction.CountA(Range("OnlyFiltMonsters1")) = 0 Then 
        Worksheets("Options").Range("FilterInput").ClearContents 
        Sheet23.Range("FilteredMonsters").Clear 
        Range("AllMonsters").AdvancedFilter xlFilterCopy, Range("FilterMonstersCriteria"), Sheet23.Range("FilteredMonsters") 
        Range("AllFilteredMon").WrapText = False 
        Sheet1.Range("G2").Value = 10 
        Sheet1.Range("G3").Value = 1 
        Sheet1.Range("G2").Value = 1 
         
        MsgBox ("Nothing found by that filter") 
    End If 
     
    ShapeStatBox 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I posted on another forum while waiting for activation to this one, and got the answer to how to make sure its never blank and I ended up with the code above. But the second question on the post was completely ignored and is still driving me nuts. It seems that my "or" option lines which remain blank are not ignored during the filter process. So if I put something in the top line to be found and leave the "or" option blank, it finds the filtered item and everything. If I just put a blank space in the bottom line it works fine again. However, I don't want the people who are going to be using the file to have access to the filter. The goal is to have the filter box's brought over either via VBA code or by simple Excel code.

Excelforum.com

I hope I explained everything clearly and followed every rule. I appreciate any help that can be given.

I have a sheet on which i have a drop down list (data, validation). The macro i am running takes the value from this cell (country) and then filters the data for this country and saves it to a new file.

If they click on the macro button, and the country field is blank, i want an error message to appear saying "please select a country". Then i want it to come out of the macro when they confirm the message. (If the country is selected correctly then the macro would have fully run and saved the file).

How do i put this into my code.

The start of my code is:


	VB:
	
 
 
Application.Goto Reference:="Country_DD" 
 'pass the value of this reference to country
country = Application.ActiveCell 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Then this goes on to filter on my data sheet and copy and save etc etc.


No luck finding an answer? You could always try Google.