Excel row height (Conditional formatting?)

Posted on 2009-04-16
Last Modified: 2012-05-06
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.

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
             rngRow.RowHeight = 12.75
          End If
       Next rngRow
    End Sub

    Open in new window

    LVL 59

    Assisted Solution

    by:Saurabh Singh Teotia
    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

    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
                Rows(i).rowheight = 4
            End If
        Next i
    Application.ScreenUpdating = True
    End Sub

    Open in new window


    Author Comment

    by:timamartin I am going to show my novice ability - where do I insert/use the coding shown above?
    LVL 85

    Expert Comment

    by:Rory Archibald
    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).
    LVL 59

    Expert Comment

    by:Saurabh Singh Teotia
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    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.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now