Solved

Excel row height (Conditional formatting?)

Posted on 2009-04-16
681 Views
I have a spreadsheet where every other row is a different height and I would like to know if there is a way to set it up so I don't have to set each row individually. There are only two row heights, 12.75 and 4 and they alternate.

snap01001.jpg
0
Question by:timamartin

LVL 85

Accepted Solution

You can't do it with conditional formatting but you can do it with a macro, such as this (select your data first):

``````Sub FormatRowHeights()

Dim rngRow As Range

For Each rngRow In Selection.Rows

If rngRow.Row Mod 2 = 0 Then

rngRow.RowHeight = 4

Else

rngRow.RowHeight = 12.75

End If

Next rngRow

End Sub
``````
0

LVL 59

Assisted Solution

Assuming you want to start from row-1 till the 100 row you can use the following code it will do what you are looking for, Formating the row-1 to 12.75 and next to 4 and so on till row-100
Saurabh...

``````Sub rowheight()

Application.ScreenUpdating = False

Dim i As Long

i = 1 ' Your starting row

For i = 1 To 100

If Application.Evaluate("mod(" & i & ",2)") = 1 Then

Rows(i).rowheight = 12.75

Else

Rows(i).rowheight = 4

End If

Next i

Application.ScreenUpdating = True

End Sub
``````
0

Author Comment

Ok...now I am going to show my novice ability - where do I insert/use the coding shown above?
0

LVL 85

Expert Comment

If you press Alt+f11 to open the VBEditor, then choose Insert-Module from the menu and paste that code into the window that appears. You can then run the macro in Excel by pressing f8 and selecting it from the dialog, assuming your macro security is not set to high in the Trust Center (under Excel Options).
0

LVL 59

Expert Comment

One small correction in rory comment, to run the macro you need to press alt+f8 on your which will open the macro window, showing the macro which you just inserted, Just select the same and hit run which will run the macro and do what you are looking for.
0

Featured Post

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.