Question for the real experts!

What does Excel do AFTER i calculated the sheet with (f9). (calculation
is set to manual)

I have the following problem:
In cells F2-I5000 (and R2-U5000  AC2-AF5000  AO2-AR5000) are formulas
reffering to cells in A2-E5000.(and m1-q5000 x1-ab5000 aj1-an5000)
In if I clear cells A2-E5000 (and the other ranges) with a macro then
before F9 it is really fast.(1 second)
After F9 it's really slow(12 seconds)

The marco is this:

Cells(1, 1) = Now()
Range("A2:e5000").Select
Selection.ClearContents
Range("m1:q5000").Select
Selection.ClearContents
Range("x1:ab5000").Select
Selection.ClearContents
Range("Aj1:an5000").Select
Selection.ClearContents
Range("A1:a1").Select
Cells(1, 2) = Now()


So before F9 its fast and after i hit F9 its really slow.
I think ->Selection.ClearContents  is what is slow.



ok thanks
frank
diepenbos@yahoo.com


-- 
diepenbos
------------------------------------------------------------------------
diepenbos's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21124
View this thread: http://www.excelforum.com/showthread.php?threadid=354763

0
3/16/2005 1:18:14 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
397 Views

Similar Articles

[PageSpeed] 1

try

Range("A2:e5000,m1:q5000,x1:ab5000").ClearContents

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"diepenbos" <diepenbos.1lznoc@excelforum-nospam.com> wrote in message
news:diepenbos.1lznoc@excelforum-nospam.com...
>
> What does Excel do AFTER i calculated the sheet with (f9). (calculation
> is set to manual)
>
> I have the following problem:
> In cells F2-I5000 (and R2-U5000  AC2-AF5000  AO2-AR5000) are formulas
> reffering to cells in A2-E5000.(and m1-q5000 x1-ab5000 aj1-an5000)
> In if I clear cells A2-E5000 (and the other ranges) with a macro then
> before F9 it is really fast.(1 second)
> After F9 it's really slow(12 seconds)
>
> The marco is this:
>
> Cells(1, 1) = Now()
> Range("A2:e5000").Select
> Selection.ClearContents
> Range("m1:q5000").Select
> Selection.ClearContents
> Range("x1:ab5000").Select
> Selection.ClearContents
> Range("Aj1:an5000").Select
> Selection.ClearContents
> Range("A1:a1").Select
> Cells(1, 2) = Now()
>
>
> So before F9 its fast and after i hit F9 its really slow.
> I think ->Selection.ClearContents  is what is slow.
>
>
>
> ok thanks
> frank
> diepenbos@yahoo.com
>
>
> -- 
> diepenbos
> ------------------------------------------------------------------------
> diepenbos's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=21124
> View this thread: http://www.excelforum.com/showthread.php?threadid=354763
>


0
Don
3/16/2005 1:45:13 PM
try this as first line

ActiveSheet.DisplaypageBreaks = false
and/or

application.ScreenUpdating = False

code
reset to true
-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Don Guillett" <donaldb@281.com> wrote in message
news:OJaLi5iKFHA.4032@TK2MSFTNGP14.phx.gbl...
> try
>
> Range("A2:e5000,m1:q5000,x1:ab5000").ClearContents
>
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "diepenbos" <diepenbos.1lznoc@excelforum-nospam.com> wrote in message
> news:diepenbos.1lznoc@excelforum-nospam.com...
> >
> > What does Excel do AFTER i calculated the sheet with (f9). (calculation
> > is set to manual)
> >
> > I have the following problem:
> > In cells F2-I5000 (and R2-U5000  AC2-AF5000  AO2-AR5000) are formulas
> > reffering to cells in A2-E5000.(and m1-q5000 x1-ab5000 aj1-an5000)
> > In if I clear cells A2-E5000 (and the other ranges) with a macro then
> > before F9 it is really fast.(1 second)
> > After F9 it's really slow(12 seconds)
> >
> > The marco is this:
> >
> > Cells(1, 1) = Now()
> > Range("A2:e5000").Select
> > Selection.ClearContents
> > Range("m1:q5000").Select
> > Selection.ClearContents
> > Range("x1:ab5000").Select
> > Selection.ClearContents
> > Range("Aj1:an5000").Select
> > Selection.ClearContents
> > Range("A1:a1").Select
> > Cells(1, 2) = Now()
> >
> >
> > So before F9 its fast and after i hit F9 its really slow.
> > I think ->Selection.ClearContents  is what is slow.
> >
> >
> >
> > ok thanks
> > frank
> > diepenbos@yahoo.com
> >
> >
> > -- 
> > diepenbos
> > ------------------------------------------------------------------------
> > diepenbos's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=21124
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=354763
> >
>
>


0
Don
3/16/2005 3:21:51 PM
Reply:

Similar Artilces:

XML DOM question
Hi All, I use XML DOM to read and write XML files. I did not find any specification whether or not XML DOM locks files for reading and writing at the moment it reads or writes to the file. I have a system where different applications can read and write from and to XML files using XML DOM, so it is important to me to know whether or not the file that is being read is locked for writing and vice versa. Does anybody can help me with this issue or refer to a proper article? Thanks a lot in advance. -- Mark. How about singlenton pattern in your situation? We can't use singlenton th...

Mouse Rollover question
In our CRM 4.0 system, as you roll your mouse over a field lable (or hover over it), an information box pops up with the field name. This apparently is from the Department of Redundancy Department, as telling me the same thing as I already know is pointless. However, each attribute has a description field that should be used for further details about that field. Is there any way to get the value in the description field for an attribute to appear in the roll-over box, rather than the field name itself? This can be especially useful when a field name needs to be shorter than what we ...

Newbire Question: How to Transfer Outlook Address Book To Another PC?
Hello, I'm a newbie to Outlook as I've never used it before. I'm setting up a laptop for a friend and he wants his outlook address book transferred onto his laptop from his old PC. I have no idea how to do this, so if someone could help me or maybe there is a link that has the information, it would be appreciated. jeff <Zombieboy@hotmail.com> wrote in message news:so3dl1tv64qnr8d738l0t5brk4kto2pp5m@4ax.com... > Hello, > > I'm a newbie to Outlook as I've never used it before. I'm setting up a > laptop for a friend and he wants his outlook address...

Questions on Exchange 2003 (smtp & exch attributes)
Good day, Well bit by bit my exchange migration is proceeding. I'm quite impressed with the tools available for this procedure and hope the project wraps up without incident (touch wood). Having said that, I do have 2 questions - one on smtp (5.5 <> 03) that wasn't really covered in the deployment guide and another on recipient exchange attributes (which is just due to my inexperience with the product). SMTP. - Our 5.5 server (192.168.0.1) is currently running IMS and a smtp connector to the 2003 box, mail flows both ways fine. Our firewall routes mail from our valid...

007 questions
How do I add some new chart styles (say, with different line widths)? Is it possible to record a macro that changes the chart size? (I've recorded it, but nothing happens when I run it) Has anyone developed a data labels macro or add-in yet? I haven't gotten around to investigating the new chart template mechanism. I have gone through macro recording, though. Do you still have 2003? Record the macro there. It may not take into account the new formatting features, but the macro still ought to work in 2007. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solut...

Event Sounds Question
I assign wav files to system events. Certain ones won't play when when I assign it to the Windows Logon event. It previews fine and plays in Windows Media Player I thought the length was the problem 'cause these wav files are 22 and 24 seconds but I assigned a wav file to the Windows Logon that is 30 seconds and it plays fine. Although the length does have an impact. Any ideas?? James ...

FE/BE question
Hi, When an environment has a single FE and BE, during the initial setup, do the permissions change on Exchsrvr folder of either Exch server, or do they simply retain the default permissions (as seen in KB 322935)? thanks, Mel On Thu, 12 Oct 2006 04:54:02 -0700, Mel <Mel@discussions.microsoft.com> wrote: >Hi, > >When an environment has a single FE and BE, during the initial setup, do the >permissions change on Exchsrvr folder of either Exch server, or do they >simply retain the default permissions (as seen in KB 322935)? > >thanks, > >Mel The perms st...

Question #2
Hello! I'm new to the group and I'm working on an excel sheet. What I'm trying to do is, in a column have a number ex. 36 repeat that number 72 times and on the 73 row increase the number 36 by 1 (to 37). I need to do it about 730 times. I don't know where to start. I've tried to do an auto fill but it's increments by a decimal. The sheet should look like this. Column A 36 36 36 36 36 (72 times) 37 37 37 (72 times) Can anyone help? Thanks, Darrell --- Message posted from http://www.ExcelForum.com/ One way, put in row1 =FLOOR(36+(MOD(ROW(1:1)/73,72)),1)...

Using LVN_HOTTRACK question
In CListCtrl, is it possible to prevent the item color change (if using LVN_HOTTRACK notification) when mouse hovering over an item? Thanks in advance ...

$$$ MONEY REAL QUICK $$$
Do you want to make MONEY REAL QUICK ? Let me share the links that made me a fortune and will make you rich, too. In just two years you can make one Million Dollars: USD 1.000.000.- Check out these money machines: http://cash2k10.fapturbo.hop.clickbank.net http://cash2k10.forexrobot.hop.clickbank.net http://cash2k10.forexace.hop.clickbank.net All of these programs have been thoroughly tested by thousands of users across the US and Europe. These programs do work: Just with http://cash2k10.fapturbo.hop.clickbank.net alone I have made a staggering USD 6287.- within 10 days. Yes. You will ...

outlook 2003 question
Hi All, I am using windows xp home sr3 with outlook 2003. I have several e-mail accounts one I use with outlook express the other I use with outlook 2003. Can I set up outlook with the two accounts and have them go into separate in boxes? Can this be done? Any help would be most appreciated. thanks, -- Regards, Gary Metzler send to: gmtravel@bellsouth.net http://www.outtasighttravel.com Msn messenger: gmetzler1150@hotmail.com Skype name: garymetz pop accounts? or? If pop accounts generally you create folders to sort mail on receipt / send, appropriately ...

Simple Valication Check... Question
Using VS2008 I have an app that has an edit box that contains the Clients Age. I'm doing the validity check when the edit box looses focus. This works great when the user clicks a button that runs the application. However, if the user clicks Run App from the menu, the edit box does not loose the focus... therefore the check is not made. I can tell you that the code for File>Run does nothing more that call the ::OnFileRunapplication What's the easiest way to resolve this? Seems like I need to loose the focus of the edit box before making a call to ::OnFileRunap...

Are you a VISIO expert?
3 servers a firewall Cisco router Cisco VPN concentrator Remote users I have a class project to complete. I hate my visio diagram. The firewall pic is a burning firewall. I need help. Please e-mail me at taishi_bak@hotmail.com. Please give me some assistance or examples. Any suggestions? Warm Regards, T What type of help are you looking for? Rick ...

Couple of outlook 2000 questions
I have a couple of Outlook 2000 question I would appreciate the answer to if anybody can help, Outlook is standalone and not connected to an exchange server: When starting a new e-mail the reciepents name is entered in the to box e.g. tim then the subject is entered but the name does not change to the e-mail address even though Tim is in the address book?? How do I get the address of the sender of an e-mail to be entered in to my address book when I reply to their e-mail. Tim Rather a confusing post. You did not post enough information here to permit a guess, let alone an answer. Outlo...

OL 2003 Data File question
Hi. Right now I have one .pst file for my Outlook and everything runs fine. My question is this: Does Outlook 2003 run slower as the .pst data files grow larger. I have many emails I need to keep for business reasons and although it might be ok now, will my Outlook run slow, say, a year or so from now; once the .pst file has grown to say a gigabyte or more, etc.? Any help would be appreciated. Thanks. Andrew ...

VB to have first letter Capital Question
I'm using a data input form to add records to my table. I'm collecting address information and want the first letter of each word to be capital in the address field. I searched this site and found some code the would accomplish this however when I use it, I get errors. I'm wondering if it is because the address contain numberic values, then a street name followed by Street, Lane, Blvd. etc. Can you take a look at this and see why it is generating an error.. Private Sub CHAPADD_AfterUpdate() If StrComp(Me!txtCHAPADD, LCase(Me!Text), 0) = 0 Then Me!txtCHAPADD = StrConv...

Access 2007 and MDE file Question
I have a Access 2003 MDE file that links to an Access 2003 MDB file. I understood that Access 2007 is not able to work with an MDE file. This is according to Microsoft KB article: http://technet.microsoft.com/en-us/library/cc178973.aspx However, unbenknownst to me, one of my customers somehow installed Access 2007 on a machine and ran the MDE file with it. I saw it with my own eyes! The application had been working fine for years, but now all the sudden they claim it is "losing" data. First, how come it can even run the MDE file? Second, could the Fact that i...

Simple Access Query/Form Question
Hello all, I know this may be a stupid question but i'm a newbie to Access. Here the background on what I'm designing. I'm creating a database to track special orders for our store customers. Employees enter information about the order including customers name, contact information, item to be ordered and etc. The main form has multiple check box to note if the customer has paid, the order has been placed, arrived, when the customer was notified and when the customer picks up the item. I'm looking to create a query that checks the field to see if a specific check box is checke...

autonumbering question
hi all, I have office 2007. Im using access. I have two colums of numbers, the first, column 1 is for a context number, and i enter it by setting a value manually in design view i.e. 140. i want to enter a list of items numbered 1 to 50 in the second column, column 2. when i use auto number it lists the sequence but when i change the value in column 1 for the next context i.e. 142... the numbers contuinue in colum 2 i.e. 51 52 53 but i want them to start again at 1 2 3 etc... how do i do this? any help is greatly appreciated well - as you have found, you can't use Autonu...

2 questions about connectors and replication
We are currently running an exchange server that replicates to another exchange server in Frankfurt, Germany. It has been setup in Exchange and the DNS records so that anyone sending or receiving email goes through the german server first and our server second. What we would like to do is sever the replication and host our own email without going through them first. My first question is whether or not there is anything I need to do as far as our exchange server goes, besides removing the "frankfurt" connector and adding a new local connector? The second question is what is ...

When Excel workbook starts I want to ask a question first
Thanks for trying to help.... When a coworker opens an Excel file, I want Excel to first have the person enter a number... Once the number is entered, that number is matched from column A and the sheet will only display the contents of that matched "row". Am I making sense? We just don't want to open the spreadsheet with all that data in thier face and have them search for their own stats. Again thanks! Hi Steve, Probably a couple of ways, depending on how the data is assembled. Perhaps a combination of a drop-down list with the numbers and a vlookup formula. Or a workshe...

GP Expert Needed
Hello, My company is looking to hire a GP Expert for a period of 2-3 weeks to resolve some ongoing issues that have been occurring since we implemented GP 8.0 in February. We would need somebody with in depth knowledge of the GP database and report writing. We are located in Nyack, NY (Rockland County) Is anybody interested or possibly have any leads as to where I could find such a resource? (Other than Microsoft directly?) Thank You, Lina Weiss Database Administration Manager Stoffel Seals Lina, We are a Great Plains partner located in NJ and have a ton of GP and reporting exper...

Mac user sending Japanese characters are received as question mark
I have a Mac user using Safari to access OWA hosted on a Exchange 2003 server. He can read and type Japanese characters but when he sends a new message or a reponse to a message the Japanese characters are received as question marks. Does anyone have any idea how to fix this? Steve <Steve@discussions.microsoft.com> wrote: >I have a Mac user using Safari to access OWA hosted on a Exchange 2003 >server. He can read and type Japanese characters but when he sends a new >message or a reponse to a message the Japanese characters are received as >question marks. Does any...

Another IMF V2 question
I just reinstall SP2 on my Exchange 2003, and I still don't see the IMF option under my default SMTP virtual server. And YES I have checked the box (Apply IMF filter) in the Advanced propreties on the Default SMTP virtual server. Anyone has an idea? REgards JP The only thing on the advanced properties of the SMTP virtual server is to turn on/off the different filtering options. IMF settings are found under ESM > Global Settings > Right Click on Message Delivery > Intelligent Message Filtering tab. "JP Breton" <jpbreton@videotron.ca> wrote in message ...

Can't edit SQL question in MS Excel/Query
Hello, I'm running Win2k SP3, Excel 2000 SP-1 and have created SQL questions that fetch data from an Oracle 8i DB via ODBC. Sometimes (or actually quite often) I can't edit the SQL questions in MS Query. The MS Query icon just flashes in the taskbar and disappears. Any wiz got any idea what can be done about it? Regards Hans Hans I'm not sure about the MS Query icon you are talking about in the taskbar but the way I can get to MS Query using Excel 2000 and connectiong to Oracle database is choosing 'Data' followed by 'Get External Data', and then choosing '...