Automatically change row height?

Hello.

The user types text stuff in A1. If there is no text, the row height
for A2 is zero/minimal. If there is text, A2's row height autofits to
that text. Some users enter more text than others, so A2's height has
to grow or shrink depending on what is in A1. The formula in A2 is IF
(A1="","",A1&", "&F16)


Is this possible without VBA? If not, does anyone have any good code
for this?

Thanks!

VR/Lost
0
cpocpo1 (22)
2/10/2009 5:42:14 PM
excel 39879 articles. 2 followers. Follow

3 Replies
569 Views

Similar Articles

[PageSpeed] 13

Private Sub worksheet_change(ByVal target As Range)
Set target =3D Rows("2:2")
If Range("A1").Value <> "" Then
target.AutoFit
End If
End Sub


On Feb 10, 10:42=A0pm, Lostguy <cpo...@mail.com> wrote:
> Hello.
>
> The user types text stuff in A1. If there is no text, the row height
> for A2 is zero/minimal. If there is text, A2's row height autofits to
> that text. Some users enter more text than others, so A2's height has
> to grow or shrink depending on what is in A1. The formula in A2 is IF
> (A1=3D"","",A1&", "&F16)
>
> Is this possible without VBA? If not, does anyone have any good code
> for this?
>
> Thanks!
>
> VR/Lost

0
muddanmadhu (119)
2/10/2009 7:30:20 PM
Right-click on your sheet tab and paste the following code into the module

Private Sub Worksheet_Change(ByVal Target As Range)
    Set isect = Application.Intersect(Range("A1"), Target)
    If Not isect Is Nothing Then
        If Range("A1") & "" = "" Then
            Rows("2:2").RowHeight = 0
        Else
            Rows("2:2").EntireRow.AutoFit
        End If
    End If
End Sub

-- 
Steve

"Lostguy" <cpocpo@mail.com> wrote in message 
news:d6427f72-4585-4ea0-8ae9-560f5bb53572@l33g2000pri.googlegroups.com...
> Hello.
>
> The user types text stuff in A1. If there is no text, the row height
> for A2 is zero/minimal. If there is text, A2's row height autofits to
> that text. Some users enter more text than others, so A2's height has
> to grow or shrink depending on what is in A1. The formula in A2 is IF
> (A1="","",A1&", "&F16)
>
>
> Is this possible without VBA? If not, does anyone have any good code
> for this?
>
> Thanks!
>
> VR/Lost 

0
AltaEgo
2/11/2009 12:52:42 AM
Forgot one step

Right click on the sheet tab, "PRESS VIEW CODE", paste the code

-- 
Steve

"AltaEgo" <Somewhere@NotHere> wrote in message 
news:e7MZNM#iJHA.1184@TK2MSFTNGP04.phx.gbl...
> Right-click on your sheet tab and paste the following code into the module
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>    Set isect = Application.Intersect(Range("A1"), Target)
>    If Not isect Is Nothing Then
>        If Range("A1") & "" = "" Then
>            Rows("2:2").RowHeight = 0
>        Else
>            Rows("2:2").EntireRow.AutoFit
>        End If
>    End If
> End Sub
>
> -- 
> Steve
>
> "Lostguy" <cpocpo@mail.com> wrote in message 
> news:d6427f72-4585-4ea0-8ae9-560f5bb53572@l33g2000pri.googlegroups.com...
>> Hello.
>>
>> The user types text stuff in A1. If there is no text, the row height
>> for A2 is zero/minimal. If there is text, A2's row height autofits to
>> that text. Some users enter more text than others, so A2's height has
>> to grow or shrink depending on what is in A1. The formula in A2 is IF
>> (A1="","",A1&", "&F16)
>>
>>
>> Is this possible without VBA? If not, does anyone have any good code
>> for this?
>>
>> Thanks!
>>
>> VR/Lost
> 
0
AltaEgo
2/11/2009 12:55:50 AM
Reply:

Similar Artilces:

remove 1st 2 letters in each row
I have 30 rows and each row I need to delete the first 3 letters. ex UNE127 UNE50 so it will be 127 50 Can someone please tell me how I can do this without going in and deleting each row. Thanks A simple MID might suffice Assume your data in A2 down In B2: =MID(A2,4,99)+0 Copy down. The "+0" bit will convert the extracted text nums to real numbers. Success? hit the YES below -- Max Singapore --- "Cathy" wrote: > I have 30 rows and each row I need to delete the first 3 letters. > ex > UNE127 > UNE50 > &...

A program is trying to automatically send e-mail on your behalf.
I tried posting this in microsoft.public.outlook.general, but no nibbles. ------------------------------------------------------------------------------------------------------ Does anybody know how to turn the "A program is trying to automatically send e-mail on your behalf." prompte off? It pops when one of my MS Access apps is emailing one of it's reports to somebody. It happens on the Citrix server that one of my MS Apps is running on, but it does not happen when the app is running on my own PC. Consequently I'm hoping for some user-configurable setting that controls...

Automate Changing Table Name in Query
I have an Access database (several actually) where there are make table queries that have to be run every month and the table name has to be changed to reflect the month and year. ( ex. tableName_022007) I've built a form with a button that opens the query in design view to make it a little easier but I would like some way to have the name changed automatically each month so that all that needs to be done it to click the button. Any ideas? -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000, Office 2003 and Visual Studio 2005 In a normalized database, a tab...

Changing Case In Excel
If you type in the wrong case in word, you can hit shift F3 and it changes the case (e.g. lower to caps etc). Is there any way to do this in Excel? If your text is in say, A1. At a different cell, say B1,you can use the following formula: 1) to change the string to upper case: =upper(cell reference) 2) to change the string to lower case: =lower(cell refernce) 3) to change the string to sentence case: =proper(cell reference) Then copy and paste value of B1 to remove the formula. Lynn >-----Original Message----- >If you type in the wrong case in word, you can hit shift >F3...

Instances doesn't get updated when the master stencil changes
Hi, I am trying to create a stencil master shape in such way that when I change the master, all instances also get updated according to the new master shape. At this point I have a master shape in a stencil and I have hundreds of instances in different visio drawing based on the very same stencil. When I change the master shape, I have to change all instances (several hundreds) and this is such a pain. How can I solve this problem? Any help would be appreciated, Max On Tue, 17 Oct 2006 09:29:15 -0400, "Maxwell2006" <alanalan@newsgroup.nospam> wrote: >Hi, > &...

Automatic CC
Hello, Is there any way to create a template where a contact is automatically CC in the email. So I dont forget to cc them every time I send an email. Thanks ...

Money 2006 constantly faults with automatic updates
Whenever I use the Internet automatic updates, money faults out and wants to send a report to Microsoft. Also my account list shows most accounts with a status of 'Update In Progress' I've done a complete reinstall after removing it and power cycling my machine. I thought it may have been something with removing 2004 and installing 2006, but got same problem Has anyone else been having this problem? I really just want to go back to Money 2004. What's the error message? -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://money.mvps.org/faq f...

Automatic axis format
Hi, I'm need to make a automatic axis format within excel. The number of rows is depending of a imported file. No problem to get the max. number of rows. But to put this number into the chart ( category axis) is more problematic. I try VB but my knowledge is at dummy level ! Waiting for your reply............. Hi, Have a look at Jon's examples of dynamic charts. http://peltiertech.com/Excel/Charts/Dynamics.html Cheers Andy jos wrote: > Hi, > I'm need to make a automatic axis format within excel. > The number of rows is depending of a imported file. > No problem to g...

Column format to override row fomat in FrX
In FrX the row format overrides the column format for font styles eg. bold etc. it would be useful if it was possible to have the option for the column format to override the row fomat ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesss...

Changing Location of Templates
I'm running Excel 2002 on XP Pro. I want to change the location of my templates directory. "Help" says to go to Microsoft Shortcut Bar, but of course, in XP, there's no such thing. In Word, there's a tab under Tools/Options/File Locations, but not in Excel. What do I do? Thanks, Bill Hi Bill See the page from MS about Office 2002 Templates http://support.microsoft.com/default.aspx?scid=kb;EN-US;290232 ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Thanks, D...

Automatic updates, automatically disabling itself
Automatic updates, automatically disabling itself. 2nd day now onecare popped up to tell me the updates turned themselves = off. I'm thinking what malware would want to turn the updates off ? Any idea what's going on here ? Did I AUTOMATICALLY install an update = that caused this ? <keepout@yahoo.com.invalid> wrote in message news:8l7mi5tkgp1gd15fkef71ll5mrui9ukmht@4ax.com... > Automatic updates, automatically disabling itself. > > 2nd day now onecare popped up to tell me the updates turned themselves > off. Drop OneCare and install Microsoft Sec...

Subreport Height not Grow/Shrink when change Page
Hello, I have a calendar report which uses 30 copies of a sub-report to build each day. Working just fine except: Let's say January 16th, there are 2 items to be listed for that date and the sub-report grows to a height of 0.5". I hit Next Page, go to February. There are now 4 items that need to be listed in that box. But instead of growing to accomodate these 4 items, the box stays at the 0.5" from January, and thus cuts off the February data. I have set Can Grow and Can Shrink everywhere -- in the sub-report details, on the sub-report control, and on the main report details...

I open the MDB, the form runs automatically
Simple question I have an MDB file with 20 files and 7 forms in it. When I open it up, the form runs immediately an access goes into some sort of protected mode. I can't view the tables, queries etc. I can see the code by pressing Alt F11 but not the data. I created a blank db and imported the mdb so I could see the tables. But how do I open the original without it running the code? When I look at the code. I don't see anything that sets properties or invokes the code automatically. There are no macros in this, just the forms and associated code with them. Any ideas Thanks Colm ...

Find highest value in each row.
Hi, I have a problem trying to find the highest value for each row. I have a query, which adds up scores. Its based on a table something like this orgID Score1 Score2 Score3 Score4 Score5 Score6 1 10 1 5 2 11 20 2 1 4 2 12 11 24 1 10 7 15 12 1 2 and so on..... I have query that basicly adds up some of the scores into groups. So its like: orgID ScoreSet1:[Score1]+[Score2]+[Score3] ScoreSet2:[Score4]+[Score5]+ [Score6] 1 16 ...

can't change print styles
In outlook 2003 when I try to change how a calendar prints, I click file-page setup-daily style - I get an error "unable to display dialog box". Any ideas as to how I can fix this? It happens with all the headings under page setup. Thanks, Jennifer ...

How do I get an automatic equal sign to begin the formula bar?
How do I get an automatic equal sign to appear in the formula bar in Excel for Microsoft 2003? This happened for me in the 2000 version and I could just click on it with my mouse pointer, but now all that appears is the function sign with no equal sign after it. It makes my job a lot more time consuming to have to type in the equal sign while building formulas. Please help, anybody? The = icon disappeared in newer versions of excel (xl2002???). You can add an = icon Tools|customize|commands tab|Insert category (Scroll down the list until you find the = icon) And drag it to your fav...

My worksheet automatically adds a fill color when I enter text.
When I type text in a blank cell, the cell automatically is highlightes with the bright yellow fill. There is no conditional formatting on this workbook. Check your VBA editor. You might have an Event Macro which does what, usually, a CF does. Try to Increase the Macro Security to its maximum level in order to eliminate the Event Macro from running... You may, also consider to "rem" all the Macro commands by adding a Preceding apostrophe to each command. Micky > When I type text in a blank cell, the cell automatically is highlightes with > the bright yello...

Input locale changes to English only when a dialog box with IExplore control is closed
Hi, I found that the Input Locale changes to English only mode when a dialog box with IExplore control is closed. How can I prohibit this unexpected behavior? Please reply. Thanks in advance. Regards, Hyun-jik Bae ...

Changing color (background & foreground)
Looking for help with a color setting on a report. I'm designing a report in which the background & foreground colors for a single control need to change based on a database / query produced value. Each record in the query contains the background & foreground numbers that I want a specific control to use when printed. For example, green = 44544. How can I get ACCESS to change these attributes dynamically? My database is running in Access 2000. Thanks in advance for any help. I had it figured out approx 2 years ago, and forgot how I did it! This "getting old thing&qu...

Having numbers automatically appear bold
Hi all, Had such great advise last time, thought I'd try again. I have a large range of cells where I will be inputing sales figures i the form of currency revenue. The range is about G-I and 1-400. Th sales data will be a comparisson of YTD 2001 - 2004 revenue results. What I hope to do is have any figure that is above $30,000 appea automatically in bold so as to stand out as a good client. I gues there is something in the IF function to do this but I'm stumped. Any ideas? Thanks again in advance. Saxte -- Message posted from http://www.ExcelForum.com select the range you wa...

Change to lastname first in GAL
This seems like it should be simple, but I cannot find where to change the display format of the global address list. It formats with firstname lastname instead of lastname firstname... I must be missing something obvious...can someone please point me in the right direction... Thank you kindly. Try this: http://www.msexchange.org/tutorials/MF023.html -- Mark Fugatt "Shawn" <anonymous@discussions.microsoft.com> wrote in message news:3b2c01c48f94$a3b38700$a301280a@phx.gbl... > This seems like it should be simple, but I cannot find > where to change the display for...

Format a data point without changing the legend
I want to format a data point of a series in excel but when I do that the legend changes completely. For example: all data points are from "series 1" in the legend. When I format one data point from this series, then the legend no longer displays "series 1" but instead it gives the X-values of all data points. How can I make sure that the legend will still display "series 1" instead? The thing is that in all my previous scatter-plots, I am able to change a data point and still have "series 1" in the legend. I do not understand why it is changed ...

live mail changes extension?!
Something weird started happening yesterday on my wife's computer (XP pro): When right-clicking a word document in explorer and going to sent to-->mail recipient, Windows Live mail opens as usual, but the attached file now has a .._doc extension instead of .doc. I have received such attachments in the past and ascribed them to the ignorance and clumsiness of the senders, but this apparently happens indeed as an error on the part of Windows, Live Mail, or some other agent. Any ideas? (I posted this also on the Windows file system forum) thanks, Danny Afaik, Window...

Is it possible to automatically move data entered in columns into rows?
Is it possible to automatically move data entered in columns into rows? It seems like a simple thing to do but I can't find a function anywhere. Cheers, Richard Thorneycroft Husky Products If you enter something in ONE cell it is automatically in a row and a column. So, perhaps a bit more explanation is in order. -- Don Guillett SalesAid Software donaldb@281.com "Richard Thorneycroft" <dancecommander81@hotmail.com> wrote in message news:9e2f3f75.0411160750.7003c6ab@posting.google.com... > Is it possible to automatically move data entered in columns into rows? >...

Automatic Forward
How can I automatically forward messages received from a specific sender to another's e-mail person? Thanks, ...