Rows().AutoFit starts a calculation, but Columns().AutoFit doesn't

This example copied from the help of the WorkSheet.Calculate event works well:
Private Sub Worksheet_Calculate()
    Columns("A:F").AutoFit
End Sub

But it loops forever when I replace it with:
Private Sub Worksheet_Calculate()
    Rows("10:20").AutoFit
End Sub

It looks like resizing the rows (even rows outside the UsedRange) causes the 
volatile functions to be calculated, while resizing the columns doesn't.

Does it make sense?
How can I resize the rows after a calculation?

Thanks,
Stefano
0
Utf
11/25/2009 4:12:02 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
765 Views

Similar Articles

[PageSpeed] 5

Hi,

I can't replicate your problem but this should stop recursive calls

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
     Rows("10:20").AutoFit
Application.EnableEvents = True
End Sub

Mike

"Stefano" wrote:

> This example copied from the help of the WorkSheet.Calculate event works well:
> Private Sub Worksheet_Calculate()
>     Columns("A:F").AutoFit
> End Sub
> 
> But it loops forever when I replace it with:
> Private Sub Worksheet_Calculate()
>     Rows("10:20").AutoFit
> End Sub
> 
> It looks like resizing the rows (even rows outside the UsedRange) causes the 
> volatile functions to be calculated, while resizing the columns doesn't.
> 
> Does it make sense?
> How can I resize the rows after a calculation?
> 
> Thanks,
> Stefano
0
Utf
11/25/2009 4:31:04 PM
I just found out that disabling the events doesn't loop forever, but a second 
calculation still restarts. It doesn't finish, and the result that a lot of 
cells have the wrong values.

If I set a breakpoint on a user function, I see that the correct value is 
displayed in the cell, then the  second calculation starts and:
- with EnableEvents = False it replaces many values with zeros
- with EnableEvents = True it loops forever

I wasn't able to find a way to use the Row().AutoFit in the Calculate event, 
even if I could use the Column().AutoFit

Sadness,
Stefano

"Mike H" wrote:

> Hi,
> 
> I can't replicate your problem but this should stop recursive calls
> 
> Private Sub Worksheet_Calculate()
> Application.EnableEvents = False
>      Rows("10:20").AutoFit
> Application.EnableEvents = True
> End Sub
> 
> Mike
> 
> "Stefano" wrote:
> 
> > This example copied from the help of the WorkSheet.Calculate event works well:
> > Private Sub Worksheet_Calculate()
> >     Columns("A:F").AutoFit
> > End Sub
> > 
> > But it loops forever when I replace it with:
> > Private Sub Worksheet_Calculate()
> >     Rows("10:20").AutoFit
> > End Sub
> > 
> > It looks like resizing the rows (even rows outside the UsedRange) causes the 
> > volatile functions to be calculated, while resizing the columns doesn't.
> > 
> > Does it make sense?
> > How can I resize the rows after a calculation?
> > 
> > Thanks,
> > Stefano
0
Utf
11/30/2009 5:01:01 PM
Reply:

Similar Artilces:

2 questions one on list/combo boxes and the other on "atomically" hiding columns or rows.
I followed most of the post recently on the subject of list and combo boxes. From what I can tell I need to create a list but I need to have the list find the item and than a price associated with that item and than it's cost. Is there some where that I can find away to do that? The other question on the opening page of my quote sheet I have by columns the floors (4) of the house and under those a list of rooms (these are the headings of the rows) that are on each floor. In the following columns I have the my wiring combination. I have all this linked to other worksheets that have simil...

Sum on top of stacked columns #2
Is it possible in some way to add the sum of series on top of each column in a stacked column diagram? I am often using this feature in ThinkCell for my powerpoint slides. However, need to insert a stacked column graph from Excel 2007 into Word 2007. Currently, I've been using a text box with a reference. However, the reference in a text box is static, which makes it a source for error when inserting or deleting rows in Excel. Hello Drews. You can add a new series with the total sum and then: Change the chart type to line ONLY for this series. Format the series without lines or markers...

how do I insert bullets in the same line(row)?
I understand how to insert bullets line by line but how do you do it so that the bullets appear on the same line. like this: * a *b *c *d *e *f Any help would be greatly appreciated. Use the Symbol dialog box (Insert | Symbol in Word 2003 or Insert tab | Symbol | More Symbols in Word 2007). -- Stefan Blom Microsoft Word MVP "jvrsshurricane" <jvrsshurricane@discussions.microsoft.com> wrote in message news:A1CA8A0A-A074-47CE-9BF7-8650707586E3@microsoft.com... >I understand how to insert bullets line by line but how do you do it so &...

Link entire row in one cell
Hi, I want to link the entire row that has data in sheet 1 to a cell in sheet 2. Is this possible and if so how do you do it? Stuck on this issue. On Mon, 8 Feb 2010 08:56:01 -0800, t wrote: > Hi, I want to link the entire row that has data in sheet 1 to a cell in sheet > 2. > Is this possible and if so how do you do it? Stuck on this issue. Sheet 1? Sheet 2? Cell? Sounds like an Excel question. You have posted this question to the wrong newsgroup. The access in this groups title refers to Microsoft Access, a database program. Please repost to the correct newsgrou...

how to delete a row in excel with a specific word using visual basic
Hi, I have an excel file that always come with a line at the end with "downloaded:" in the cell. How can I write a macro to search for that cell and delete the row that cell is in? Thanks for all your help! Any time you are in doubt on how to interact with excel, try recording a new macro and then physically do the steps you are trying to code. Stop the recording and then go look at the recorded macro www.jameswesleybluesband.com Hi "mchen" mchen@gellerco.com, Delete rows with "N" in Column 31 (col AE) from my http://www.mvps.org/dmcritchie/excel/delem...

calculate dates #2
I am trying to create an application to calculate a date that is 2 weeks from today but I never want the date to be a weekend or holiday. The results I want to run on someones desktop without them having to do anything. Any thoughts? Use =WORKDAY(TODAY(),14) WORKDAY is part of the Analysis Toolpak add-in, so you may to install that -- HTH RP (remove nothere from the email address if mailing direct) "kevin.raway@lifefitness.com" <kevinrawaylifefitnesscom@discussions.microsoft.com> wrote in message news:A790A02C-0A02-42F0-9684-719436798CD6@microsoft.com... > I a...

New Outlook user needs help getting started/installed
I've been using Outlook Express for my e-mail. I bought a new computer -came with a trial version of Outlook 2003. I liked it and now want to install Outlook from my Office XP disk. All of the other programs installed properly and run, but Outlook crashes every time. I already uninstalled the trial version of Outlook 2003. Is there something I can do to start using Outlook (it's 2002 in my Office XP bundle)? Thanks in advance, SS assuming Outlook 2002 installed correctly, see if creating a new profile works: http://support.microsoft.com/kb/287072/EN-US/ "ste...

Sum a table of columns & rows
I have a spreadsheet of 154 Rows (all unique project numbers in numerical order) and 9 columns of account numbers (some are similiar and some are user entered, therefore there could be 'blanks' with no data in them). I am trying to create a table that will only give me the project number if there are dollars in one or more of the columns. This would be used for data entry (and that is why I would like to have the columns summed up - to remove duplicates). Any ideas? I have given a brief example below: F, G, &am...

Adding up numbers from columns.
How can I sum up multiple columns while not letting each cell "associated values" sum to a number greater than one specified. For example, if I have sets of colums: 14 3 12 2 11 2 11 1 10 1 and 20 5 19 4 18 3 17 2 and 25 6 22 4 20 3 18 2 I want to know what 3 combination of numbers in the left 3 colums giv me the highest value...WITHOUT letting the correspoding numbers in th right hand column be greater than, say 10. Thanks, Larr -- Message posted from http://www.ExcelForum.com ...

Inserting Excel Data into Word Columns
Hi, I am fairly new to VBA and Excel/Word and have been trying to automat a sheet we have, I have created the Excel Sheet and unfortunately th Word doc was created sometime ago and cannot be changed. I have searched this forum and found this code: Code ------------------- Sub CreateNewWordDoc() ' to test this code, paste it into an Excel module ' add a reference to the Word-library ' create a new folder named C:\Foldername or edit the filnames in the code Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Dim i As Integer Set wrdApp = CreateObject(&qu...

column chart changes my data
Hi I have attempted to make a column chart with error bars using 6.3 0.008199 6.8 0.0151625 7.5 0.006469 as my data, BUT when i highlight and insert chart my figures (6.3,6.8,7.5) change to 1,2, and 3. and my other figures change to 1-8 I am trying to show rate of reaction vs pH level can anyone tell me what i am doing wrong as I dont normally have any trouble with these charts.I am using office 2003 -- thanks to all You should be using an XY chart for numerical value, particularly for scientific analyses. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Pelt...

counting rows depending on 2 selective values
Hi, well, I have lots of data and need to do some statistics on it. For that I need to count the number of rows "H" where a string value "X" is written but only if these rows also have cell which is NOT empty in column "E"... the if stuff drives me crazy (especially as i have a spanish version...) any help appreciated :) -- Acid-Sky ------------------------------------------------------------------------ Acid-Sky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26502 View this thread: http://www.excelforum.com/showthread.php?thr...

Excel Row as Header
I am making a table and the top row has all the headings for each column. I want this row of headings to appear on each page, since the table extends to 3 pages and users will be adding rows frequently. Is this possible, and if so, how? thank you Hi Kat, If you are refering to printed pages, then: File > Page Setup Then select the 'Sheet' tab and enter your heading row where it says 'Rows to repeat at top'. I use XL2000. Other versions might be slightly different. Regards - Dave. And if you're referring to the screen: Select cell A2 Window>Freeze Panes -- Ki...

Calculations #4
Hi, when I am calculating my totals, my sum is more than what is listed. How do I make the necessary changes to reflect the correct sum. Thanks, Jessica This might describe your problem: http://www.mcgimpsey.com/excel/pennyoff.html In article <1959a01c44d55$091c3920$a601280a@phx.gbl>, "Jessica" <anonymous@discussions.microsoft.com> wrote: > Hi, > > when I am calculating my totals, my sum is more than what > is listed. How do I make the necessary changes to reflect > the correct sum. > > Thanks, > > Jessica Thanks for such ...

Calculating Days From Dates and printing letters.
I wonder if anyone can help me? Calculating Day of Week I am trying to use Outlook as my contact management software in the entertainment business. I have several hundred potential venues that run on different days in the week. When I create a new contact I would like to be able to; Input a date the venue has booked. This could be in the past or the future. From the date I have input have outlook calculate which day of the week the venue runs and store this information. Later I want to be ale to pull up for example; all venues that run on a Wednesday in a specific county Printing letters...

Sales Tax Calculation
Hi, What function or calculation could I use in a spreadsheet to calculate the price of an item minus the local sales tax so that in the end the final price would be a whole number. I would like to enter the local tax rate in percent so I could figure out what the price for an item should be so in the end the final price is $5, $10, $15 etc. Using Excel 2000. Thanks, Linda If the tax rate is in cell B1 and the final (total) price is in B2, use this formula in cell B3 (or any other cell): =ROUND(B2/(1+B1),2) Roy "LMB" <RomulanQueen@10Forward.SSTNG> wrote in mes...

counting negative and positive percentages in a column
I am trying to figure out how to do a sumif or countif for a range of values negative and positive values of a cell in a column. example 10% 15% 33% -10% -5% -45% I would like to have one formula to count the cells with a positive % from 0.01% to 20% another formula to count the cells with a negative % from -0.01% to -20% thank you Try these: For the positive range: =COUNTIF(A1:A10,">=0.01%")-COUNTIF(A1:A10,">20%") For the negative range: =COUNTIF(A1:A10,"<=-0.01%")-COUNTIF(A1:A10,"<-20%") -- Biff Microsoft Excel MVP <vieux-lo...

Column format when merging Excel data to Word labels
I have a four column Excel spreadsheet which I have Mail Merged into Word to create labels. One of the columns in Excel contains a list of four digit numbers. Some of those numbers begin with a zero. When I have merged the data into Word labels, the four digit numbers starting with a zero have the zero missing. I have tried all sorts of number formats in Excel but can't get the zeros to stay there when merged to Word. Can anyone help???? I'm using Office 2003. There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Wo...

Using a Yes/No field to trigger a calculation in another field
I am trying to use an IIF statement to have a filed calculate based on another field having either Yes or No in it. The fields are: TotalTuitions - all the tuitions added together MultiClassTrigger - Yes/No fiield MultiClassDisc - 10% discount to be calculated depending on Yes/No IIF statement - MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0) This will work the first time that Yes is selected in the Trigger field but then the value will stay when I change the Trigger field back to No. With No selected it should change the discount field back to zero. ...

copy and paste visible columns
I am new to excel and am using the outline feature. I have collapsed the outline and want to copy and paste it to a new worksheet but It keeps on copying my whole outline , not just the columns I selected. Any help for a newbie would be appreciated Try .. In source sheet, Select the range Press F5 > Special > Check "Visible cells only" > OK Right-click on selection > Copy In destination sheet, Right-click > Paste -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "ferde" <ferde@discussions.microsoft.com> wr...

CRM Security Service not starting
I recieve an error in the event log "Error: An error occurred while attempting to update the SCP (Active Directory update). -2147016656 (0x800720030) Description: There is no such object on the server." I am running the CRM 1.2, Crystal 9, all on the same Domain Admin level ID, SQL Server is installed on a seperate server with the same domain admin id running the service. I have not installed any SPs. I have the correct MDAC 2.8 version. the Build Version table contains Build Date Timestamp BuildNumber BuildQFE 8/23/2005 7:08:01 PM <Binary>...

Row height problems
My default row height is 13.20, but everyone else in my office has a default of 12.75. I cannot even get on the same scale as them, mine goes up and down by .6 and theirs adjusts by .75 Our fonts are the same, and I can't find out where to change this. Just a guess... Do you use the same printer/printer driver? TimLeHew wrote: > > My default row height is 13.20, but everyone else in my office has a default > of 12.75. > I cannot even get on the same scale as them, mine goes up and down by .6 and > theirs > adjusts by .75 > Our fonts are the same, and I can...

start menu all programs
Hello Can someone please remind me how to organise the programs in 'all programs' in the Start menu into folders. I've done it before but cannot find out how to do it again. Thanks KK Hello KK, The tutorial below can help show you how to organize the Start Menu "All Programs" area. http://www.vistax64.com/tutorials/79571-start-menu-shortcuts.html Hope this helps, Shawn KRK;1251447 Wrote: > Hello > > Can someone please remind me how to organise the programs in 'all > programs' > in the Start menu into folders....

enter data in the same column in multiple sheets
"Sial56" <Sial56@discussions.microsoft.com> wrote in message news:03C54443-F3BC-4665-A1C0-16E45B76AA93@microsoft.com... >Nothing PLEASE write your question in the body of the post and NOT just the subject as you cannot POSSIBLY give us all the information you need in such a small space. Have a look here: http://dts-l.net/goodpost.htm Select all sheets. Then enter the data. As long as each sheet is IDENTICAL.... ...

Column comparison
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Chip Pearson has lots of techniques for working with duplicates: http://www.cpearson.com/excel/Duplicates.aspx =isnumber(match(a1,sheet2!a:a,0)) will return True if the value in A1 (of sheet1) matches any value in column A of sheet2. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > t...