Solved

Auto set every other row height in large spreadsheet

Posted on 2014-07-30
10
1,805 Views
Last Modified: 2014-07-30
I have excel sheet that has 1000 rows and I want to format every other row to a different height?  Can I do this with an "If" statement?  I know there has to be a way to accomplish this just not sure how.
Thanks,
G
0
Comment
Question by:glophillips1
10 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40229511
Just  follow the instruction as follows
Click the Menus tab;
Click Format drop down menu;
Click Row item;
Then you will view the AutoFit Row Height item staying in the sub-item list of Row item. The Auto Fit Row Height is the right thing we are looking for.

Duncan
0
 

Author Comment

by:glophillips1
ID: 40229570
Duncan,
I tried that and nothing happens when I select Auto Fit Row Height.  What am I doing wrong?
Gloria
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40229575
it should be okay if you are using Excel 2010 above excel 2007

You can not find tab  or you've  other issue ? please advise

Duncan
0
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
LVL 10

Expert Comment

by:FamousMortimer
ID: 40229586
Is this what you are looking for?  You can add this to the Sheet code you would like to adjust the row heights on.

Sub SetRowHeights()
    Dim i As Integer
    
    For i = 1 To Range("A1048576").End(xlUp).Row
        If i Mod 2 = 0 Then Rows(i).RowHeight = 25
    Next i
End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40229594
I found this arcitle for similar issue for Excel 10 at yahoo site
http://voices.yahoo.com/how-auto-fit-column-width-row-height-in-12467758.html
0
 

Author Comment

by:glophillips1
ID: 40229600
Duncan,

Okay, I go to Home, Format, then AutoFit Row Height in Excel 2010.  When I select AutoFIt Row Height nothing happens.  Am I in the right place?

Gloria
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40229603
Could you capture your excel image to us ?

Duncan
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40229723
FamousMortimer's solution works and is fast (although, one only needs to adjust rows up to 1000, not the end of the sheet).

There is no Excel function available that will control row height.  However, one could add a helper column to identify the rows to be adjusted and then filter and adjust accordingly.

To do it manually:
1) Add or insert a helper column.  This can be deleted later.
2) In this helper column, go to the first row to be adjusted and enter the following formula:
=MOD(ROW(),2)
Note the result; it will either be 0 or 1.
3) Copy this formula down to row 1000 (or however many rows you need).
4) Turn on data filters and filter on the value (0 or 1) that is for the rows you want to adjust the height.
You should only see every other row.
5) Select all the visible, filtered rows.
6) Change the row height:
    a) Right-click on any row header
    b) Select "Row height..."
    c) Enter the new height
7) Turn off the filtering
8) Delete or hide the helper column (optional)

I've attached an example file that demonstrates this.

Regards,
-Glenn
EE-rowheight.xlsx
0
 

Author Comment

by:glophillips1
ID: 40229749
I want even number rows to be 19.5 in height and the odd number to be at the default.  I have all rows mared with a mod function so they are either a 1 (odd) or 0 for even.   I think Famousmortimer your suggestion will work but I don't know how to get to that point.  Can you help.
Thanks,
Gloria
0
 

Author Closing Comment

by:glophillips1
ID: 40229766
This worked great!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do you check for multiple possibilities in Excel 7 29
A Table within a User Form 24 48
excel conditional formating 4 18
NEED TRANSFER  DATA 59 24
My experience with Windows 10 over a one year period and suggestions for smooth operation
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question