Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height
Hi All,
Can anyone help with this. Is there a piece of code that will change the height of the Row/Rows if there is data in columns. For example: If I had data from cells C6 through to C100, only those rows would be 15 instead of the default of 12.75. Because the data is forever changing, I would want the rows to change with it. Sounds rather pointless I know, but can it be done Many thanks in advance Regards John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height
This macro will scan down column C and if the cell is totally empty it will
adjust the height to the default height 12.75, else row height will be 15. Sub RowSizer() Dim lngLastRow As Long Dim rng As Range lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row For Each rng In Range("C1:C" & lngLastRow) If IsEmpty(rng) Then rng.RowHeight = 12.75 Else rng.RowHeight = 15 End If Next rng End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "JohnUK" wrote: Hi All, Can anyone help with this. Is there a piece of code that will change the height of the Row/Rows if there is data in columns. For example: If I had data from cells C6 through to C100, only those rows would be 15 instead of the default of 12.75. Because the data is forever changing, I would want the rows to change with it. Sounds rather pointless I know, but can it be done Many thanks in advance Regards John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height
The following procedure will make the row height resizing take place
automatically as you enter or remove data in the range C6:C100. Right click the tab at the bottom of the worksheet you want to have this functionality, select View Code from the popup menu that appears and then copy/paste the following code into the code window that appears... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C6:C100")) Is Nothing Then If Len(Target.Value) Then Target.RowHeight = 15 Else Target.RowHeight = 12.75 End If End If End Sub Now, go back to your work sheet and enter something into any cell in the range C6:C100 and watch the row height change.... now delete it and watch the row height return to the default size of 12.75. -- Rick (MVP - Excel) "JohnUK" wrote in message ... Hi All, Can anyone help with this. Is there a piece of code that will change the height of the Row/Rows if there is data in columns. For example: If I had data from cells C6 through to C100, only those rows would be 15 instead of the default of 12.75. Because the data is forever changing, I would want the rows to change with it. Sounds rather pointless I know, but can it be done Many thanks in advance Regards John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height
You could actually do this too without the loop.
Sub RowSizer() Dim lngLastRow As Long lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row ' adjust all row height to 15 Cells.RowHeight = 15 ' adjust blank cell rows in Col. C to 12.75 Range("C1:C" & lngLastRow).SpecialCells(xlCellTypeBlanks).RowHeig ht = 12.75 End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "JohnUK" wrote: Hi All, Can anyone help with this. Is there a piece of code that will change the height of the Row/Rows if there is data in columns. For example: If I had data from cells C6 through to C100, only those rows would be 15 instead of the default of 12.75. Because the data is forever changing, I would want the rows to change with it. Sounds rather pointless I know, but can it be done Many thanks in advance Regards John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Row Height
Hey! You are both stars
Many thanks for your help "Rick Rothstein" wrote: The following procedure will make the row height resizing take place automatically as you enter or remove data in the range C6:C100. Right click the tab at the bottom of the worksheet you want to have this functionality, select View Code from the popup menu that appears and then copy/paste the following code into the code window that appears... Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C6:C100")) Is Nothing Then If Len(Target.Value) Then Target.RowHeight = 15 Else Target.RowHeight = 12.75 End If End If End Sub Now, go back to your work sheet and enter something into any cell in the range C6:C100 and watch the row height change.... now delete it and watch the row height return to the default size of 12.75. -- Rick (MVP - Excel) "JohnUK" wrote in message ... Hi All, Can anyone help with this. Is there a piece of code that will change the height of the Row/Rows if there is data in columns. For example: If I had data from cells C6 through to C100, only those rows would be 15 instead of the default of 12.75. Because the data is forever changing, I would want the rows to change with it. Sounds rather pointless I know, but can it be done Many thanks in advance Regards John . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Set a minimum row height a the same time with autofit row height | New Users to Excel | |||
Row Height stopped growing and Auot-Fit Row Height does not work | Excel Discussion (Misc queries) | |||
the row height should be excatly the height of the data | Excel Discussion (Misc queries) | |||
Resizing row height to dynamically fit height of text box | Excel Discussion (Misc queries) | |||
resize row height and column height | Setting up and Configuration of Excel |