How do I get a filter to "UPDATE" the rows selected?

Hey there!
I am using Excel 2002 and I have a speadsheet that has prices in it.

If I have the filter set to (the filter criteria is much more complex than
this though) prices for items that cost >$5.00.

Of I change an item to 4.59, it should not be seen. How can I get it to
re-update the rows shown, with out releaseing the filter and resetting it?
Is there something like refresh.filter or anything like that?

Thanks
Phil


0
Phillips
11/18/2003 12:53:32 AM
excel 39879 articles. 2 followers. Follow

3 Replies
579 Views

Similar Articles

[PageSpeed] 24

AFAIK you must release and reset but a macro with a worksheet_change event
could trigger this for you so that it would happen automatically.

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Phillips" <Phillips*NOSPAM*@RecycledReefs.com> wrote in message
news:g4eub.229720$Fm2.229809@attbi_s04...
> Hey there!
> I am using Excel 2002 and I have a speadsheet that has prices in it.
>
> If I have the filter set to (the filter criteria is much more complex than
> this though) prices for items that cost >$5.00.
>
> Of I change an item to 4.59, it should not be seen. How can I get it to
> re-update the rows shown, with out releaseing the filter and resetting it?
> Is there something like refresh.filter or anything like that?
>
> Thanks
> Phil
>
>


0
Don
11/18/2003 1:18:35 AM
I forgot to mention that you should NOT post in all ngs as it wastes the
time. However, if you must, the way you did it is best.

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Phillips" <Phillips*NOSPAM*@RecycledReefs.com> wrote in message
news:g4eub.229720$Fm2.229809@attbi_s04...
> Hey there!
> I am using Excel 2002 and I have a speadsheet that has prices in it.
>
> If I have the filter set to (the filter criteria is much more complex than
> this though) prices for items that cost >$5.00.
>
> Of I change an item to 4.59, it should not be seen. How can I get it to
> re-update the rows shown, with out releaseing the filter and resetting it?
> Is there something like refresh.filter or anything like that?
>
> Thanks
> Phil
>
>


0
Don
11/18/2003 1:22:55 AM
YOu can use a worksheet_Change event, similar to the following. The code 
is stored on the worksheet module of the sheet that contains the filter 
(right-click the sheet tab, and choose View Code)

'==================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim filt As Filter
Dim Op As Long
Dim rng As Range
Dim ws As Worksheet
Set ws = ActiveSheet
If ws.FilterMode = False Then
   Exit Sub
End If
Set rng = ws.AutoFilter.Range

If Intersect(Target.EntireColumn, rng) Is Nothing Then
   Exit Sub
Else
   i = Target.Column
    If Not ws.AutoFilter.Filters(i).On Then
      'do nothing
    Else
      Set filt = ws.AutoFilter.Filters(i)
      On Error Resume Next
       Op = filt.Operator
       If Op = 0 Then
         Range("A1").AutoFilter Field:=i, _
           Criteria1:=filt.Criteria1
       Else
         Range("A1").AutoFilter Field:=i, _
           Criteria1:=filt.Criteria1, Operator:=Op, _
           Criteria2:=filt.Criteria2
       End If

     End If
End If

End Sub
'==================================

Phillips wrote:
> Hey there!
> I am using Excel 2002 and I have a speadsheet that has prices in it.
> 
> If I have the filter set to (the filter criteria is much more complex than
> this though) prices for items that cost >$5.00.
> 
> Of I change an item to 4.59, it should not be seen. How can I get it to
> re-update the rows shown, with out releaseing the filter and resetting it?
> Is there something like refresh.filter or anything like that?


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/18/2003 2:04:11 AM
Reply:

Similar Artilces:

Update Query SQL Help
I am getting stuck on the SQL code to do this. What I want to do is Prompt a user for input a value for "City" on table "Shipping" and update the field City on table shipping to this new value. there is an ID feild that will always be set to 1. any help? "Bombay"wrote: >I am getting stuck on the SQL code to do this. > > What I want to do is Prompt a user for input a value for "City" on table > "Shipping" and update the field City on table shipping to this new value. > > there is an ID feild that will always be set to ...

Textboxes on subform won't update
Hi, I have a form with a multiselect listbox(List17) and I have a query qry_Projects_ListBox that contains the selections from this listbox. I have a subform (sbfrm_Projects_ListBox) that contains text fields from qry_Projects_ListBox. I placed the subform on my main form and what is supposed to happen is when the user selects options from the listbox, I use a command button to requery qry_Projects_ListBox so the items will populate on the subform, but when I do this the subform does not update, but if I open the subform separately the records are there. My code to requery looks like. I am...

Repeated update failures hidden
When repeated failures for 3 updates started in mid-October, I changed auto-update to download-but-ask-to-install. Mid-November, I turned auto-update back on and now there are repeated failures for 6 updates. I am concerned by repeated install attempts and failures. So now, by right clicking failed updates, I have hidden them. I will try to restore them at some future time and try again. IS THIS OK? yogibearbull wrote: > When repeated failures for 3 updates started in mid-October, I changed > auto-update to download-but-ask-to-install. Mid-November, I turned > auto-...

Unable to see updated values in CRM
I added some new values to a form and saved it but they don't show up in CRM. I'm probably missing a simple step and need to know what that step is. Any help is appreciated. ...

E-Pay update window locks?
Hello -- I'm having trouble getting my online payment to work properly. My regular updates seem to work fine, and I've asked it to schedule some A-Pays, which it did successfully, but that I then later deleted. Now I'm trying to send a regular Epay, and the update window just stays up and locks, ultimately forcing me to close the whole application with Task Manager. When I log on to my bank through a regular browser I see that the payment has been scheduled, but it's still registered as unpaid in the Bill Pay area in Money. I then told it to try paying it again, t...

Update external links prompt upon opening
I see that Excel 2002 & later allow you to change the startup settings for updating external links so that you will not be asked and the links will not be updated automatically. Is there a way to do this in Excel 2000? You could use another workbook that contains a macro to open your real workbook. You can specify what you to do in that macro. Option Explicit Sub auto_open() Workbooks.Open Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=0 ThisWorkbook.Close savechanges:=False End Sub Nate wrote: > > I see that Excel 2002 & later allow you to cha...

automated way to update the hyperlinks
Is there some automated way to update the hyperlinks in all documents on our server there are 1200 of them. We tried to follow a link that I had changed in the a document and it wouldn't work until he actually went in the link, edited it, followed the file, and clicked ok. Of course, many of these are just references and not actual links but to do a manual update or at least figure out which files to prioritize seems a little overwhelming. Some one mentioned they thought there was a way to make these links dynamic. ...

Status Change Workflow
Hello, I'm trying to make a workflow which would update a field upon deactivating the record. The workflow really is simple but doesn't seem to work because (I'm guessing) the workflow is ran after the record is deactivate and you can no longer write in it. Am I correct? I might try to do this logic within a ONSAVE event, but then again, the OnSave is ran before deactivating the record thus I cannot know in the script if the record is active or not. Thanks for your help ...

Software update not successful
Operating system: Windows Vista 32-bit Home Premium. I installed MS Money Plus 2008 and activated it successfully. When I open the program, I get the message "To continue operating successfully, Money requires a software update". When I try to update, I get the message "Microsoft Money Update Utility has stopped working" and the program closes. Has anyone else had this problem or have a solution/suggestion? Have you enabled DEP? If so, either switch it back to the default "windows services only" settings or add the following executables to the exception ...

Windows XP (NL): Windows Activation Problem + Windows Automatic Update Problem = Very Slow Computer.
Hello, Today I visitted my mother for a nice dinner, after the dinner I wanted to show her something on the computer/internet. I noticed how the computer was extremely slow, and how the harddisk was rattling non-stop. First I was wondering if it was infected with a trojan or a scanner, this did not appear to be the case. Then I immediatly started wondering about windows automatic update since I never trusted it to function properly... I have seen my share of failures with it... however the computer is pretty much unmanaged so at least automatic updates protected it a l...

Large file sent many times / SP 2 update.
Outlook 2003 (11.6359.6408) SP 1. XP Home Sending large files >1MB results in the file being send many times (28 in one case). One solution suggested was to turn off the AV when sending. This was done and seemed to help but the problem has returned. Checked for updates and found SP2 was available. Tried to install but got the message "sku112.cab" was not found. Put my MS/Office Small Business CD as requested but the install still fails. 1. What can I do to send large files? 2. How can I install SP2 for Outlook update? Greg #2 get the full file from Microsoft to i...

Fix My Post Update Online Financial Services Communication Error!
After many attempts and many phone calls to MS Money Tech support I finally got my old MS Money 2005 file to load with the new automatic update. I was experiencing all the same problems everyone else was, while converting the old file I got the Online Financial Services communication error…….etc, etc. At least for me, my problem was because of a closed account that was setup to be used with MSN bill pay. (I used MSN Bill Pay at one point but stopped using it a while ago.) I simply changed the Financial Institution on the closed account from MSN Bill Pay to something else and the file c...

How do I update charts using macros
I have lot of charts (44 in total) in one sheet. The data for the charts are linked to another spreadsheet. When I update the values the charts are not updated even though both files are open at the same time. It will only update if I manually re-visit the data ranges or close the file and re-open it. Is there are any why to telling (using macros) to update all the charts in the workbook? Thanks in advance Seng Hi, Is the Calculation mode set to something other than Automatic? Cheers Andy Seng wrote: > I have lot of charts (44 in total) in one sheet. The data for the charts are &...

Update query to populate new field with specific ID
I have a table with AcctNo, SecurityName, otherfields. I need to update this table with a new field that would indicate for each AcctNo row the number of SecurityName. for example: ACCTNO NEWFIELD SECURITYNAME 10021 1 IBM 10021 2 SEARS 10022 1 MICROSOFT 10022 2 RIMM 10022 3 GMC Is there a function that can do this? this is a one time deal, I intend to create a new table with the results, at a later point I may have to refresh the data at that point I will redo the table. I need to run some stats for each Account an...

Monitor doesnt work after windows update
I was installing an update and left- when i came back my computer was off and when i restarted it asked if i wanted to run sfe mode or regular- i chose regular and when the screen came back on it was blank except for the mouse arrow.(which moves) when i go back into safe mode the same thing happens-- my moniter shows the screen saver- which i had tuned off- and when i move the mouse it turns off but nothing comes n screen except the mouse arrow-- help??? please? gsgurch wrote: > I was installing an update and left- when i came back my computer > was off and when i res...

Using workflow to update related enties fails
I have a CRM 4 setup with a custome entity called Training Courses and to those I can associate oppertunities (as bookings on a course) I can run a workflow on a booking and access (update or read) the associated Course but I need to be able to run a workflow against the aprent Course and have it update all the associated opertunities. Initially it wasn't an option in a workflow created against the course entity. So I created a 1:N referential relationship in the oppertunity entity to link them. I can now see the status of my oppertunities from my Course workflow but when I try and ru...

Insight looping after online updates
Hi, Anyone else seeing this? It's happened to me more than once so I'm not running Insight any longer while Money is running... -- John Any one else see this or does it work well most of the time? "JohnA" <John@TheAtas.org-spamguard> wrote in message news:%23Td0ysO4HHA.5804@TK2MSFTNGP05.phx.gbl... > Hi, > > Anyone else seeing this? It's happened to me more than once so I'm not > running Insight any longer while Money is running... > > -- John > ...

update query
I need some help please...if possible just what I need to put in the design view of the query, as I am not familiar with SQL codes. I want the query to look match the names and amounts on Table 1 to Table 2, and where the Names and Amounts are equal to update Table 2 with the Voucher numbers in Table 1 Table 1 Table 2 Name Name Voucher No Voucher No Amount Amount 156 records 1056 records Grateful for your help Cristina -- Add both tables to the query -- Drag from Name to Name to establish a join -- Drag from Amount to Amount ...

Getting an update query to run automatically
Hi, I wish to get an update query to run automatically (and first) when other queries are run. In the database are queries which are dependant on a table (Table A). This table contains information from another table (Table B) which changes daily. A query has been created to update my table (Table A) from the original (changing) table (Table B). Other queries have been created which play around with the information from my table(Table A). So in order for these other queries to be up to date there is a need for the update query to be run first and, hopefully, automatically. Where on earth do ...

Entourage Crash After MS Update
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Just updated Entourage as per the MS automatic update. Now Entourage crashes (log below) <br><br>Is there any solution short of uninstalling and reinstalling Office? I have not been using TimeMachine, although I will certainly start now... <br><br>Thanks in advance for any help. <br><br>Michael <br><br>Process: Microsoft Entourage [1537] <br> Path: /Applications/Microsoft Office 2008/Microsoft Entourage.app/Contents...

Good grief! Which are ESSENTIAL updates?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Since many of us cannot get Auto Updater to work and some have resorted to 'manual' updates, it becomes really important to know which updates are essential and which have been combined into later updates. After a fresh install of 12.0.0 with no subsequent updates working, which updates must I install for the next update to work? For instance... Do I need to scour around for 12.0.1 or was it combined into 12.1.0? And what about all the others? I don't even know what's been released, let alone what'...

I cant get any updates to install.
Everything is on that is to be on. Any light would be great, thanks. -- Skip Rock Maybe you have them all? "Skip" <Skip@discussions.microsoft.com> wrote in message news:4CCB3033-4CD5-4197-A8A9-C4778A6636DA@microsoft.com... > Everything is on that is to be on. > Any light would be great, thanks. > -- > Skip Rock Skip wrote: > Everything is on that is to be on. > Any light would be great, thanks. Speaking of light... Start button --> RUN (no "RUN"? Press the "Windows Key" + R on your keyboard) --> type...

Issues with Chase updates
I have read all the comments earlier abou Capital One and it appears folks have got that working. I am still having a problem with Chase downloads. It says it is updating, and it gives me the new balance and the number of transcations downloaded. But when I go to reconcile the transactions they are not there. I have tried doing this every way - from money, from chase, via Qif, etc and nothing works. The last updated tranaction I received was on 11/3. Any ideas? Try deleting you temp files and redownload. Cindy "Money" <fionacraddock2000@yahoo.com> wrote in messag...

Make User Date update on 24/7 Great Plains session.
I'm using Great Plains 8. I have a 24/7 SOP Great Plains process running on the server. The orders imported use the 'User Date" to set the imported order's 'Order Date' value. The User Date does not increment on it's own. Can anyone tell me a way to update the User Date on this un-attended Great Plains session? Thanks! Kevin R How about a GP macro that changes the user date and then add that macro to run using Windows Scheduled Tasks? Never tried it but should work. "Kevin R" wrote: > I'm using Great Plains 8. I have a 24/7 SOP Gr...

Before Update in Form Property
How do I place 2 validation macros in the form property-Before Update. One of the form fields is restricted to a look-up table list the other is not. I am only able to make the validation work for the field that is not selecting from a lookup table. The validations are: Macro Name: Mcr_hours-worked Condition: [Hours Worked is Null] Action: MsgBox Action: GotoControl Macro Name: Mcr_personnel Condition: [Pers is Null] Action: MsgBox Action: GotoControl Is there a way to put both of these in the form properties - Before Update. ...