Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing row height conditionally
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing row height conditionally
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. -- HTH... Jim Thomlinson "CDAK" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing row height conditionally
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" wrote: 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. -- HTH... Jim Thomlinson "CDAK" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing row height conditionally
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 -- HTH... Jim Thomlinson "CDAK" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "CDAK" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing row height conditionally
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 "CDAK" wrote: 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" wrote: 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. -- HTH... Jim Thomlinson "CDAK" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing row height | Excel Discussion (Misc queries) | |||
need to dbl row height without changing height of cell next to it | New Users to Excel | |||
changing tab color conditionally | Excel Discussion (Misc queries) | |||
Conditionally format row height | Excel Programming | |||
Changing color conditionally | Excel Programming |