Conditionally format row height

From looking around it appears as though row height cannot be conditionally
formatted without some VB script. Here is my dilemma and I was hoping that
someone could give me an example of some code if it isn't too complicated or
time consuming.

I am importing data from a SQL query with 7 columns and a variable number of
rows. One of the columns is "Type". There are 3 possible types: Requirement,
Enhancement and Defect. With the first two types, I would like the row height
to be large so that I can see all the information in the cells for a given
row (i.e., I don't want anything truncated). However to cut down on the size
of the spreadsheet, I would like to adjust every row that is under type
"defect" to be smaller (say row height = 30).

Thanks for any assistance
Chris

Hi,
Something like this .....

Sub SetRowHeight()

' Assume data is in column B (change to your requirement)

Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

For Each Cell In rng
If Cell.Value = "Defect" Then
Cell.RowHeight = 20
Else
Cell.RowHeight = 40
End If
Next
End Sub


HTH

"CDAK" wrote:

> From looking around it appears as though row height cannot be conditionally
> formatted without some VB script. Here is my dilemma and I was hoping that
> someone could give me an example of some code if it isn't too complicated or
> time consuming.
>
> I am importing data from a SQL query with 7 columns and a variable number of
> rows. One of the columns is "Type". There are 3 possible types: Requirement,
> Enhancement and Defect. With the first two types, I would like the row height
> to be large so that I can see all the information in the cells for a given
> row (i.e., I don't want anything truncated). However to cut down on the size
> of the spreadsheet, I would like to adjust every row that is under type
> "defect" to be smaller (say row height = 30).
>
> Thanks for any assistance
> Chris
>

This should be close... It assumes type is in Column C and that you are using
"Sheet 1" for your data. You can change both of those at the set statements...

Public Const m_cDefect As String = "Defect"
Public Const m_cRowHeightSmall As Integer = 30
Public Const m_cRowHeightlarge As Integer = 60


Public Sub AdjustRowHeight()
Dim rngCurrent As Range
Dim wksCurrent As Worksheet

Set wksCurrent = Sheets("Sheet1")
Set rngCurrent = wksCurrent.Range("C65535").End(xlUp)

Do While rngCurrent.Row > 1
If rngCurrent.Value = m_cDefect Then
rngCurrent.RowHeight = m_cRowHeightSmall
Else
rngCurrent.RowHeight = m_cRowHeightlarge
End If
Set rngCurrent = rngCurrent.Offset(-1, 0)
Loop
End Sub

--
HTH...

Jim Thomlinson


"CDAK" wrote:

> From looking around it appears as though row height cannot be conditionally
> formatted without some VB script. Here is my dilemma and I was hoping that
> someone could give me an example of some code if it isn't too complicated or
> time consuming.
>
> I am importing data from a SQL query with 7 columns and a variable number of
> rows. One of the columns is "Type". There are 3 possible types: Requirement,
> Enhancement and Defect. With the first two types, I would like the row height
> to be large so that I can see all the information in the cells for a given
> row (i.e., I don't want anything truncated). However to cut down on the size
> of the spreadsheet, I would like to adjust every row that is under type
> "defect" to be smaller (say row height = 30).
>
> Thanks for any assistance
> Chris
>

That worked really well but I wasn't clear enough. For the rows that aren't
of type defect, I would like to have a variable row height that adjusts to
fit the data in the cell. Does this make it a lot more complicated? Thanks
again for the help

"Toppers" wrote:

> Hi,
> Something like this .....
>
> Sub SetRowHeight()
>
> ' Assume data is in column B (change to your requirement)
>
> Set rng = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
>
> For Each Cell In rng
> If Cell.Value = "Defect" Then
> Cell.RowHeight = 20
> Else
> Cell.RowHeight = 40
> End If
> Next
> End Sub
>
>
> HTH
>
> "CDAK" wrote:
>
> > From looking around it appears as though row height cannot be conditionally
> > formatted without some VB script. Here is my dilemma and I was hoping that
> > someone could give me an example of some code if it isn't too complicated or
> > time consuming.
> >
> > I am importing data from a SQL query with 7 columns and a variable number of
> > rows. One of the columns is "Type". There are 3 possible types: Requirement,
> > Enhancement and Defect. With the first two types, I would like the row height
> > to be large so that I can see all the information in the cells for a given
> > row (i.e., I don't want anything truncated). However to cut down on the size
> > of the spreadsheet, I would like to adjust every row that is under type
> > "defect" to be smaller (say row height = 30).
> >
> > Thanks for any assistance
> > Chris
> >