Macro deletes formula

I have created a macro to restore the row height in a protected worksheet 
when text in a cell of the parent worksheet is deleted but when I run the 
macro it deletes the formula in the cell. The macro is:
Sub Autoheight()
'
' Autoheight Macro
' Macro recorded 12/10/2004
' Keyboard Shortcut: Ctrl D
' Dim myCell As Range

For Each myCell In Selection
    myCell.Value = AutoFit
Next myCell
End Sub
What changes do I need to make to the macro prevent the formula in the cell 
being deleted?
0
11/8/2004 12:38:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
541 Views

Similar Articles

[PageSpeed] 34

try deleting the

..value=
so you have mycell.autofit

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Pennington" <Pennington@discussions.microsoft.com> wrote in message
news:A1C49667-C1B6-413B-A84D-E36F078104D1@microsoft.com...
> I have created a macro to restore the row height in a protected worksheet
> when text in a cell of the parent worksheet is deleted but when I run the
> macro it deletes the formula in the cell. The macro is:
> Sub Autoheight()
> '
> ' Autoheight Macro
> ' Macro recorded 12/10/2004
> ' Keyboard Shortcut: Ctrl D
> ' Dim myCell As Range
>
> For Each myCell In Selection
>     myCell.Value = AutoFit
> Next myCell
> End Sub
> What changes do I need to make to the macro prevent the formula in the
cell
> being deleted?


0
Don
11/8/2004 1:22:26 PM
Don
Thanks. Tried this but got runtime error 1004
Autofit method of Range class failed

What other solution might there be?

"Don Guillett" wrote:

> try deleting the
> 
> ..value=
> so you have mycell.autofit
> 
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Pennington" <Pennington@discussions.microsoft.com> wrote in message
> news:A1C49667-C1B6-413B-A84D-E36F078104D1@microsoft.com...
> > I have created a macro to restore the row height in a protected worksheet
> > when text in a cell of the parent worksheet is deleted but when I run the
> > macro it deletes the formula in the cell. The macro is:
> > Sub Autoheight()
> > '
> > ' Autoheight Macro
> > ' Macro recorded 12/10/2004
> > ' Keyboard Shortcut: Ctrl D
> > ' Dim myCell As Range
> >
> > For Each myCell In Selection
> >     myCell.Value = AutoFit
> > Next myCell
> > End Sub
> > What changes do I need to make to the macro prevent the formula in the
> cell
> > being deleted?
> 
> 
> 
0
11/8/2004 1:48:01 PM
use this instead

mycell.autofit
mycell.entirerow.autofit

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Pennington" <Pennington@discussions.microsoft.com> wrote in message
news:902A6CE3-23AB-424E-B066-8EBDA0B9664A@microsoft.com...
> Don
> Thanks. Tried this but got runtime error 1004
> Autofit method of Range class failed
>
> What other solution might there be?
>
> "Don Guillett" wrote:
>
> > try deleting the
> >
> > ..value=
> > so you have mycell.autofit
> >
> > -- 
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "Pennington" <Pennington@discussions.microsoft.com> wrote in message
> > news:A1C49667-C1B6-413B-A84D-E36F078104D1@microsoft.com...
> > > I have created a macro to restore the row height in a protected
worksheet
> > > when text in a cell of the parent worksheet is deleted but when I run
the
> > > macro it deletes the formula in the cell. The macro is:
> > > Sub Autoheight()
> > > '
> > > ' Autoheight Macro
> > > ' Macro recorded 12/10/2004
> > > ' Keyboard Shortcut: Ctrl D
> > > ' Dim myCell As Range
> > >
> > > For Each myCell In Selection
> > >     myCell.Value = AutoFit
> > > Next myCell
> > > End Sub
> > > What changes do I need to make to the macro prevent the formula in the
> > cell
> > > being deleted?
> >
> >
> >


0
Don
11/8/2004 2:05:32 PM
I don't believe cells have an Autofit Property.

Try  Selection.Rows.Autofit

Sub Autoheight()
'
' Autoheight Macro
' Macro recorded 12/10/2004
' Keyboard Shortcut: Ctrl D
' Dim myCell As Range

Selection.Rows.AutoFit
End Sub

Gord Dibben Excel MVP

On Mon, 8 Nov 2004 04:38:02 -0800, "Pennington"
<Pennington@discussions.microsoft.com> wrote:

>I have created a macro to restore the row height in a protected worksheet 
>when text in a cell of the parent worksheet is deleted but when I run the 
>macro it deletes the formula in the cell. The macro is:
>Sub Autoheight()
>'
>' Autoheight Macro
>' Macro recorded 12/10/2004
>' Keyboard Shortcut: Ctrl D
>' Dim myCell As Range
>
>For Each myCell In Selection
>    myCell.Value = AutoFit
>Next myCell
>End Sub
>What changes do I need to make to the macro prevent the formula in the cell 
>being deleted?

0
Gord
11/8/2004 9:44:02 PM
Reply:

Similar Artilces:

macros vba question
is there a way that when I save as a file. If a cell in a worksheet says Week 1 it will copy and paste with vaules a col if if says week 2 is will copy and paste with values a different col in the worksheet You can do this with a macro. I am assuming you mean a particular cell on a particular sheet, not just any cell on any sheet. The following code checks cell A1 on Sheet1 whenever you save the workbook. If A1 contains Week 1 then column D is copied & pasted in place as values. If A1 contains Week 2 then column E is copied & pasted in place as values. Private Sub W...

Macro security level
hello friends i need a VBA thats tests the macro security level and if it is up t low level to make the marcro security level is low -- frs ----------------------------------------------------------------------- frsm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3123 View this thread: http://www.excelforum.com/showthread.php?threadid=54368 You cannot change the security level through VBA. It would be pointless having it if you could. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "frsm" <frsm.281yby_1148038200.751...

deleting email accounts on outlook
help, please! i was successful in adding 3 email accounts to outlook but now i am not happy using it. i want to delete the email accounts (which i already found the help tip for) but i noticed that when i go into the email site directly (ie comcast) all the items in the inbox were transfered & emptied into outlook when i imported it. my ? is, will the inbox items transfer back into the original email inbox it came from? or will i lose all of it? i have 2-POP3 and 1-IMAP accounts to delete. thanks! Any pop mail downloaded will be lost, (Though retained in the outlook data file,...

Records should first be deleted logically from MSCRM database!
When records are accidently deleted by users, these records get physically deleted from the MSCRM database with the current config of the system. This I think can be very frustrating when users who are just learning tend to delete records they dont intend to. I think, data should first be deleted logically (ie be flagged as deleted using a bit number in a seperate field) and if need be the administrators could easily run a query and change the status from deleted to not being deleted. This could keep data intact. A job could automatically run every so many months to ensure that the reco...

Junk Mail won't delete or move
For some reason the emails in my Junk Mail folder won't allow anything. Can't move or delete them. I've changed my options to discard junk mail to keep it from building too large. Any ideas? Did you try right click, "Empty Junk"? Did you try to open the folder, highlight all messages, and drag them to the recycle bin? "Panic" <wrong@cox.net> wrote in message news:hgj722$581$1@aioe.org... > For some reason the emails in my Junk Mail folder won't allow anything. > Can't move or delete them. I've changed my options to ...

Hyperlinks static but formulas are not. Why?
Hi, If I set up hyperlink from one cell to a cell in another wksheet (same workbook), then the link doesnt get updated if I change the name of the target wksheet. On the contrary if i write a formula in sheet1 of cell A1 -- =Sheet2!D15 -- then when i change the name of the worksheet Sheet2 to NewSheet2, the formula in sheet1 of cell A1 also gets updated to -- =Newssheet!D15 How is it that formulas get updated on changing name of the wksheet, but hyperlinks dont get updated? -- Thanks a lot, Hari India >>How is it that formulas get updated on changing name of the wksheet, but >&...

Formula
There isa command to allow a formula to refer to a number in a cell to adjust the column that is used in the formula. BUT I CAN"T FIND IT! If I want the formula to be using cell A11, I want to have 11 in a cell and have it refer to whatever to A11. If I have 12 in the cell then I want it to refer to A12 instead. Sorry, I am not explaining the scenario very well. Help. MD -- mdalby ------------------------------------------------------------------------ mdalby's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7055 View this thread: http://www.excelfor...

Excel 2007: Pivot Table to SSAS why menu item "Options"->"Formulas" is disabled?
I am using Excel 2007 with SQL Server Analysis Services 2005 SP2. I want to create calculated measure based on other measures, but I cannot do this for Pivot table. If I go to menu "Options"->"Formulas" I can see that it is disabled. Same menu is enabled if I create Pivot table based on cells in sheet. How can I create caclulated measure for Pivot Table based on SSAS? Thank you ...

Mail Merge macro
How can I record a macro, with a pause, and then resume the macro to make a mail merge. Data for merge is stored in Excel. Form is set up in Word. I can't get the macro to record. I'm not sure if this is true, but I think I'd record the macro while in MSWord. I would think that it would be easier there (from someone who hasn't done this). If you think that it might be true, you may want to post in one of the MSWord newsgroups. Wonder Women wrote: > > How can I record a macro, with a pause, and then resume the macro to make a > mail merge. Data for merge is...

Macro shortcut for Database Add-In
I am trying to create a macro that turns my F12 key into a shortcut for ALT-S-R in all my workbooks. I use that series of keystrokes often through Essbase, a Database add-in used through Excel. ALT-S-R are the keystrokes to retrieve data out of a database. If no range is highlighted it puts the top left corner of the data in the active cell; however, if you hit ALT-S-R while there is a range of cells highlighted, you will only populate those cells in the highlighted range with the data. I know at least some version of what I'm trying to get is possible. I had it working for a little whi...

Insert Row macro not working
I have the following macro set up but it keeps hanging up on the first indented line. Sub Insertrow() ' ' Insertrow Macro ' Macro recorded 10/6/2005 by DCTC ' ' Keyboard Shortcut: Ctrl+i ' Dim iLastRow As Long Dim i As Long iLastRow = ActiveSheet.Cells(Rows.Count, "F").End(x1Up).Row For i = iLastRow To 1 Step -1 If Cells(i, "F").Value = "Total" Then Rows(i + 1).EntireRow.Insert End If Next i End Sub a cursory look suggests you need to change 1 to l -- Don Guillett SalesAid Software donaldb@281.co...

Macro needed, Two saves in One
Hi, I need some help. I have a Visio page open and I have saved it once already as a VSD file. So it already has a filename with a aVSD extension. So I'd like to be able to click a macro button that does this Application.ActiveDocument.Save then this Application.ActiveWindow.Page.Export "C:\Documents and Settings\Visio Stuff\PNG\SameFileNameAsVisioFileName.png" i.e. Export the file (keeping the same filename as the VSD name) but as a PNG in my PNG subfolder . Any help appreciated. (Should I move this to a VBA newsgroup ?) Jojo On Mon, 26 Mar 2007 13:46:58 +...

A macro to show images
I got a Macro for Showing images : when getting number of pics in mail, by clicking the macro button it creates an HTML with all the attached pics. It is a great macro, and it works fine for me for the last 2 weeks, today after rebooting my system the macro has stop working. Why ? When I run it in the Visual Basic editor (embedded in the outlook) it says that the macro option is disabled. I changed my security to Medium and restarted outlook(2003), then I had a message box asking me to choose whether to enable macros or disable them, I chosed Enable Macros option. Though it still does not wor...

Macro to auto save file using the format YYMM
Hi, I recorded a macro that automatically saves a file then change the formula in cell A1 to today's date, ie =today(), then save the file. I have a problem with the last part...is there any way I can set the macro such that the file will be saved as "c:\YYMM ABC" where 'YYMM' is the format of the date (probably can be taken from cell A1) and 'ABC' is my own file name? Thanks! You can get the date right in the code: dim myFileName as string myfilename = "C:\" & format(date,"yymm") & " ABC.xls" activeworkbook.saveas fi...

How to delete rows...
Hi I would like to know how can I delete all rows containing cell with specific value. I have a single sheet with 20000 rows. Thanks in advance -- Mare ------------------------------------------------------------------------ Mare's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27644 View this thread: http://www.excelforum.com/showthread.php?threadid=499658 Apply autofilters and select the value you want to remove in the pull-down list on the appropriate column. Then highlight all the visible rows and Edit | Delete Row. When you select "All" in th...

User deleted from active directory
All, Client deleted crm user in AD. Now, trying to reassign leads to any other crm from the crm user who's AD object was deleted causes an effor referencing that it can't find an object in AD... hmmmmm. Any suggestions appreciated. thanx The following KB article explains the issue and how to get a hotfix that fixes the problem. http://support.microsoft.com/default.aspx?kbid=835319 Jagan Peri Microsoft Business Solutions CRM This posting is provided "AS IS" with no warranties, and confers no rights. "joem" wrote: > All, > > Client deleted crm...

Disable DELETE PO?
Is there a way to turn off the DELETE option for POs? We don't want the data removed from the data tables, only voided. Thanks, in advance, for your help. G G, I do not know of a way to turn of the option to Delete a PO. What I do know is that if the PO is printed, it changes the status from New to Released. At this point you can no longer Delete the PO. You would need to change the status to canceled. If you have selected to maintain history, this would keep a record of the PO in history. You might also try security access to delete PO. I checked quick and c...

MAcros in resource.h
Hi, I would like to know how works the number system of macros in the file "resource.h", because I have a lot of problems with the numbers assigned to macros, having a lot of same number for different macros... and I'd like to change manually the numbers. Thanx I edit resource.h on a regular basis (please everyone, don't yell). The purpose for this, in my case, is to group together certain sets of functionality. I even leave 'gaps', editing the 'next value' settings at the bottom of the file. The gaps make it easy for me to reassign values in sequence...

Dragging cell's formula changes BOTH referenced cells
Sorry for the long post! Please see the attached Word doc - it has a table that I copied from a spreadsheet I'm working on. I included each cell's formula to help explain what I'm trying to do. As you can tell, I'm creating a kind of fuel calculator/comparison chart. I want to be able to compare the amount spent over a year for a couple of vehicles with a certain mpg rating. There's more to the chart, but this information is sufficient for my question/problem. I'm including fuel prices from $1.50 to $4.00 (man, can you believe prices these days?! :eek: ). That's...

Help: I need a macro to add words every 3 lines
Hello, I understand nothing to macro but I am sure that a macro can resolve my problem. I have the following columns, for example (it is for a dictionary database): House Maison [empty line] Car Voiture [empty line] Dog Chien [empty line] etc..... The first word is always in English and the second is always french. There is only 1 column but many lines. I would like to add "\en" in front of each English word, and "\fr" in front of each French word (in the same cell - I do not want to add a colum)... as a result I should get : \en House \fr Maison \en Car \fr Voitu...

Variable-Length dependent formula
Hi all- I have a specific (and I believe, difficult problem) I have an Excel program that does several calculations on a data set that is an input from another program. Background: The basic gist is that I have a program that varies voltage sequentially and measures the corresponding current. The program then outputs these points in a text file seperated by commas. The user then copies this information into the Excel sheet and runs a macro in order to do all the calculations on the function. Problem: Previously, all files were in the same format: for example, it swept the voltage from 0V...

How can i protect a formula and still allow copy and move data?
i have 12 columns with data on days and each new day i want to add a new day to the set of 12 columns without adding a 13th one. So the oldest day are removed and the view is still 12 days. In these columns some values are calculated with formulas from data residing in the column (not from other columns) and i want to protect the formula but allow the column to be moved one step to the left. As of now i havent been able to use the built in functions of protection of cells and protection of formulas. Any ideas? You could just move (copy-paste) the direct entry data i.e. the d...

Keeping track of useful macros
Being new to visual basic; what is the best way to keep track of useful macros one may want to reuse later on? Any way to organize them in VBE? Best way I know of is to get hold of code librarian that comes with Excel 2000 Developers edition. -- HTH RP (remove nothere from the email address if mailing direct) "malvis" <malvis@discussions.microsoft.com> wrote in message news:2BD924B1-7CF2-4D63-8FC3-4DF7CB236510@microsoft.com... > Being new to visual basic; what is the best way to keep track of useful > macros one may want to reuse later on? Any way to organize them...

rank formula
I'm having some trouble with using the rank(x,x:x) formula. The cells I'm referencing have a sum formula in them, and the values are < 0. When I enter my rank formula it returns #VALUE! instead of the rank. Is this because the values are less than 0? Is this because I'm trying to referance a cell that has a formula in it? Is there a workaround? Will Batman make it to Gotham in time to stop the Joker? Help!! On Oct 16, 4:17=A0pm, Matthew Dyer <matthew.e.d...@gmail.com> wrote: > I'm having some trouble with using the rank(x,x:x) formula. The cells > I'm refer...

Delete duplicates QUERY
I'm having two tables, each with one field which contains some data: ___________ Table1 Field1 Per Pert Perte Perter ___________ Table2 Field2 Pert ___________ Now, I want to make a DELETE Query in MS ACCESS 2003. I go by the "Find Unmatched Query Wizard" (1st point on Table2, 2nd point on Table1, 3rd click on Field2<=>Field1, 4rd point on Field2 to see. Then I have "Table2 Without Matching Table1" (I then change Is Null to <>"False" and show Table1.Field1 in stead of Table2.Field2)) I'll then end up with this SQL...