Changing row height conditionally

[CDAK]

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).

I can't figure out how to do this. Any help would be very much appreciated.
If the solution involves a macro or some VB script, please act like you're
talking to a toddler because I do have a little experience with VB but zero
experience with using VB in Excel. Thanks for any assistance.

 

[Jim Thomlinson]

As replied to your previous post...

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

If you have any specific questions with respect to this code reply to this
thread and we can give you a hand. This code should be added to a module.
Topper also responded and his code is distinctly similar.

 

[CDAK]

Sorry for reposting (I've been trying to figure this out for so long,
sometimes I forget where I've already posted) but that code gave me some
error. This code worked:

Sub SetRowHeight()

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

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

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

But I would really like for the row height to adjust to fit the data if it
is a requirement or an enhancement and only be a constant number if it is
defect. Do you know a workaround for that? Thanks and again I apoligize for
reposting.

 

[Jim Thomlinson]

Here is Toppers revised code. The most likely source of an error in my code
is that it was pasted into a sheet instead of a module (you can't have const
in a sheet). Or that the sheet name "Sheet 1" did not exist (you would need
to change that).

Sub SetRowHeight()

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

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

For Each cell In rng
If cell.Value = "Defect" Then
cell.RowHeight = 30
Else
cell.EntireRow.AutoFit 'Changed line...
End If
Next
End Sub

 

[Toppers]

Is it Row height or Column width which is required? O both?

Row height would normally only be (need to be) adjusted if there was a large
font or the cell alignment was defined "wrap text". (I stand to be corrected
on these!). For the latter, I don't know of a way to adjust the row height
dependent on the text wrapping , which itself is (can be) dependent on
column width.

What are the longest length text strings in your data? And does this always
occur in the same column?

In your example a row height of 400 is about a screen depth so I suuggest
altering the column width might well be in order.

HTH