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
603 Views

Similar Articles

[PageSpeed] 10

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:

starting over with correct balances
My husband and I have been getting more familiar with Money over the past 4 years and realize now some mistakes we made in the past, and we would like to fix them. Basically, our current acct balances in Money don't match with actual balances b/c we weren't very good at assigning transferring of money. So, for example, in our downloaded bank statement we assigned a credit card payment as "credit card payment" instead of "transfer to Discover" etc. So, is there a way to start over from today w/o losing all of our assigned transactions YTD, or having to man...

Start Menu
Morning, Can anyone tell me how to turn off the adaptive menus on the start menu. I am running XP SP3, classic mode. To clarify I have already turned off these menus in all applications its just on the Start Menu that I am having the issue. Thanks in advance Mike "Miketsw" <Miketsw@discussions.microsoft.com> wrote in message news:2CFC5DCD-26F0-407C-A11C-C319F44C6927@microsoft.com... > Morning, > > Can anyone tell me how to turn off the adaptive menus on the start > menu. I > am running XP SP3, classic mode. > > To clarify I ha...

Hiding blank rows
I have a spreadsheet with columns, first name, last name, home phone, business phone and cell phone. When I don't have phone information in any of the three columns I would like that column to be hidden or deleted. Can I do this in some automated fashion? Thanks. Michael try adding a column in that column put something like =if(counta(b2-d2)>0,"",1) and use autofilter to hide the 1s "mlkpied" wrote: > I have a spreadsheet with columns, first name, last name, home phone, > business phone and cell phone. When I don't have phone information in any of ...

Can you lock a drop-down cell so that it sorts with the row?
I have created drop-down cells (type of business) for my client contact list but when I sort that list alphabetically or by last contacted date the drop-down cells don't sort with it. How can I solve that issue? Thanks for the responses! Are these Data Validation dropdown lists? Where is the list range located? i.e. =$A$2:$A$20 If inside your sort area the lists should change to whatever is in A2:A20 after the sort. Tested in 2003 and 2007 Gord Dibben MS Excel MVP On Tue, 26 Jan 2010 13:19:01 -0800, Lisa in Victoria <Lisa in Victoria@discussions.micros...

Time calculations not sorting properly
Hi, a)I have data in an access database which was imported but the some of the times are stored as 1/1/1900 10:35:00 instead of just 10:35, this wreaks havoc when i try to sort them, I did see a CDec() function in access help, but is not available? I did try a CDbl() just to see and it change the other valid times to .786etc and the 1/1/1900 10:35:00 to 2.455etc, not sure what to do here? Thanks Try using the TimeValue function on the field. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "CraigJ" <craigjl2004@yahoo.co.uk&...

Creating a Formula to Format Column automatically? #5
They're just name values, pulled from a database. What was strange i that some of the formats tok correctly, others applied another condito (i.e. Sally Smith was supposed to be green but came out red)P -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 ...

Calculating entries in a group
I have a database that lists all applicants, and I have a report that sorts them according to which Institution they are attending. How can I have the report display the number of applicants that are attending each Institution. For example , Four applicants from Harvard, 10 Applicants from Columbia, ect. Use this query --- SELECT YourTable.[Institution], Count(YourTable.[Institution]) AS [CountOfInstitution] FROM YourTable GROUP BY YourTable.[Institution]; -- KARL DEWEY Build a little - Test a little "amandap83" wrote: > I have a database that lists all applicants, and ...

Macro for merging rows
I have a fairly large spreadsheet that are sorted based on a file # (ie: E0800100, E0800101). The spreadsheet is setup to where each entry is on an individual row as seen below: A B C E0800100 Review.... 1.0 (hr) E0800100 Review.... 2.0 E0800101 Review.... 1.5 E0800102 Review.... .5 I am trying to organize the spreadsheet so that there is only one row per file number and the Descriptions (B) and Time (C) extend along the columns of that row. A. B....

To find the Last row
The following is the pattern of the data I have. I need to know which is the last row which has the data A1 - AAA A2- BBB A3 - <blank> A4 - CCC A5 - DDD A6 - <Blank> A7 - <Blank> A8 - <Blank> A9 - EEE. In the above example I want to get the row number as 9 as the last row which has the data. Can this be acheived by some formula. I tried COUNTA, but it does not serve my purpose. Please help. Thanks Anand ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Excel...

Calculate single sheet only?
I am using excel 2002 and have a large workbook litteredwith complicated formulas. As I input large amounts of data, the calculation ( I have it set to calculate on close of F9 only) time takes 3-4 minutes, as it is calucating every formula in every sheet. I want it to only calculate on the active sheet, which will speed up the process of data entry. Then once the data has been entered, it can take its time calculating the entire workbook. Any ideas? Thanks! Alan Hi Alan, SHIFT-F9 Or Tools>Options>Calculation tab>Calculate Sheet -- Kind regards, Niek Otten Microsoft MVP - ...

How to calculate period
Hi I know the principle , compound interest rate and the future vale. need to calculate the time required to raech the future value. wha should be the formula to arrive at the result -- mana ----------------------------------------------------------------------- manan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3421 View this thread: http://www.excelforum.com/showthread.php?threadid=54154 Use the NPER() function -- Kind regards, Niek Otten "manan" <manan.27p4rb_1147440004.7238@excelforum-nospam.com> wrote in message news:manan.27p4rb...

looking for empty row to paste a range of copied cells
Hi - I am trying to make a code that will copy a range ("A27:L27") on sheet 1 and then look for the next empty row on sheet 2 and paste it in range ("A27:L27") . I would also like the macro to insert a new blank row (or insert the copied row) for the purpose of shifting existing SUM functions on sheet 2 down. I would like those functions to be right below the copied/pasted cells every time the macro is executed. Thanks for any help - Jim A You don't Mention What column you want to sum This code will copy and paste to the fist row and then sum column D Sub Cop...

Row Limit in Excel
I work a lot with excel and I know that the row limit is 65,536, but I need more than that. Is there any way to make the number of rows infinite or at least to give me a certain amount of more rows. If there is please let me know. Thank you. -- Please help Molly, that is all there is, per sheet. -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Molly" <molly_carols@yahoo.com> wrote in message news:FD2C7921-E249-4...

Row colouring that accommodates filtering?
What can be used instead of "=MOD(ROW(),2)=1" that accommodates filtering, pls? The above gives clumps of identically-coloured rows, dependent on the particular filter criteria used. Thank you! :oD As long as there are no empty cells within the filtered list: Assume A1:B1 is the header row. A2:B10 is the data Select the range A2:B10 Conditional Formatting Formula Is: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:%23wKPl8LmHHA.4768@TK2MSFTNGP05.phx.gbl... > What can be used instead of "=MOD(ROW...

Insert row with formatting from row below instead of row above?
My default Excel template has the top row formatted as a header, with bold text and a dark bottom border to distinguish it from the rows below. So if I insert a row at row 2, the new row is formatted like the header. Then I have to select the new row and remove the bold font, remove the dark border, select the header row and reapply the bottom border. Is there a way I can change the insert default so that it takes it's formatting from the row below instead of the row above. Or better yet, so that it has no formatting at all? Hi AFAIK you can't change this behaviour -- Regards Fra...

Year to date expression/calculation
I have a report based on a query that has a number of calculated controls. The query also includes a month as txtmonth which is used in the report. data is collected half yearly in June and December. I am trying to produce a report that looks like this: June 2009 Year to date Field 1 Total 2000 2000 Field 2 Total 1000 1000 Field 3 Total 3000 3000 December 2009 Year to date Field 1 Total 1000...

2 axis column chart
Anybody who knows how to make a column chart with 2 different valu axis' -- MartinN ----------------------------------------------------------------------- MartinNT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1565 View this thread: http://www.excelforum.com/showthread.php?threadid=27190 Make the chart with two data series Right click on one of the data series in the chart; select Format Data Series, open the Axis tab; click Secondary axis It needs to data series. It you have only one then you need to reproduce the values in another column and make a...

calculating row by row
I would like to have Excel total the sum of a row 5 of randomly generated numbers in a 6th column, one column at a time. I have no problem generating the random numbers and totalling them but I would like to generate the first number and place it in the total column then when I initiate it generate the second number, add this to the first and place it in the total and so on to the 5th column. Is this possible? Many thanks. Unless I'm missing something here, this formula should do it for you. This is for row 2 on the sheet, and would go into cell F2 =SUM(A2:E2) That formula will &q...

adding every third column
i need a formaula that adds every third column i.e. formula in cell A1 adds D1,G1 ect ect can some one help with this? See your other post -- Biff Microsoft Excel MVP "gma" <gma@mircosoft.com> wrote in message news:49DC4D95-4FAA-4845-A2CF-9C0E5E07EF9A@microsoft.com... >i need a formaula that adds every third column i.e. formula in cell A1 adds > D1,G1 ect ect can some one help with this? ...

Accessing Client JavaScript after Selecting Row from Grid
Hello, I am currently working with Visual Studio 2008 and asp.net 3.0 I have the following question I hope that someone can help me with: 1) I have a GridView with rows that I read from a database. 2) The user can select a row from the grid, which fires the event: protected void MyGrid_SelectedIndexChanged(object sender, EventArgs e) { <update text field on form> <call client javascript function> } 3) I have the <update text field on form> portion of the routine working, but need to call the <cal...

Sorting Rows by Color
Excel 2003: Is there a way to sort rows by color? Put all the blue rows together, all the green together, etc? There is but you need to work it. Take a look at http://www.xldynamic.com/source/xld.ColourCounter.html#sorting -- HTH RP (remove nothere from the email address if mailing direct) "SharonJo" <anonymous@discussions.microsoft.com> wrote in message news:144901c4f9c9$f734ec80$a601280a@phx.gbl... > Excel 2003: Is there a way to sort rows by color? Put all > the blue rows together, all the green together, etc? ...

Column widths #4
To adjust the width of the columns, I first select the columns then use FORMAT/COLUMN/AUTOFIT SELECTION. I then do FILE/SAVE and FILE/CLOSE. Then, when I do FILE/OPEN, the column-widths are the same as they were before I used AUTOFIT. Why aren't the column-widths being saved? If you're opening the "csv" file from Excel the width will not get saved, since it's not an Excel file... Are you using xl97? There have been lots of posts complaining that sometimes xl97 won't keep the columnwidths between closing and reopening. One suggestion is to make sure that ...

EXcluding Zeros from the average in a row
HI I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo Hi Geo This array formula will do the job: =AVERAGE(IF(F35:U35<>0,F35:U35)) To be entered with <Shift><Ctrl><Enter> instead of <Enter>, also if edited later. -- Best Regards Leo Heuser Followup to newsgroup only please. "Geo" <Geo@discussions.microsoft.com> skrev i en meddelelse news:9C2B0B65-1AF...

Row and columns
In excel my rows are numbered 1 through XXX and my columns are also numbered 1 through XXX. On my other computers using the same current version of Excel the columns are identified using Alpha A,B,C,D etc. Anyone know how to change the columns to Alpha v.s. numbers? Joe, tools, optins, general, and uncheck R1C1 reference style -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Joe" <anonymous@discussions.mic...

Trend line calculations
Hi there, i have created a simple x,y scatter graph with a trend line. The trend line has a formula attached to it, some thing in the form of y=100x-50 What Im trying to do is to take that formula and use it in the work sheet to enter different values of x to get the y values. It cant just be typed in to a box as the data in the chart will change and therefore the trend line formula will change accordingly. Does any one know how to have a copy of the current trend line formula to use in the worksheet ??? Thanks, michael --- Message posted from http://www.ExcelForum.com/ I'm no...