Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro to adjust the row heights
I have a spreadsheet that will become quite large as time goes on so I want
to use a Macro that will make a row height = 1 when a condition is met. Column A has 4 options in a drop down (In progress, Sold, Cancelled, Declined). when the file is opened, I would like the macro to adjust the rows height to 1 for those that are "Sold/Cancelled/Declined", leaving "In progress" and cells that are blank at height=15. The table will start at row 8. Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro to adjust the row heights
In the VBA editor, double click on ThisWorkbook in the Project Window.
Paste the code below into the code window and save. Close the workbook, then reopen. Your rows should be diminished. Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Change to actual sheet name lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A1:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JeffK" wrote in message ... I have a spreadsheet that will become quite large as time goes on so I want to use a Macro that will make a row height = 1 when a condition is met. Column A has 4 options in a drop down (In progress, Sold, Cancelled, Declined). when the file is opened, I would like the macro to adjust the rows height to 1 for those that are "Sold/Cancelled/Declined", leaving "In progress" and cells that are blank at height=15. The table will start at row 8. Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro to adjust the row heights
Check your other post.
But use a custom filter to show both the "in progress" or blanks. JeffK wrote: I have a spreadsheet that will become quite large as time goes on so I want to use a Macro that will make a row height = 1 when a condition is met. Column A has 4 options in a drop down (In progress, Sold, Cancelled, Declined). when the file is opened, I would like the macro to adjust the rows height to 1 for those that are "Sold/Cancelled/Declined", leaving "In progress" and cells that are blank at height=15. The table will start at row 8. Thanks for your help. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro to adjust the row heights
I've applied the following but it doesn't seem to work, any thoughts?
Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Broker lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A8:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JLGWhiz" wrote: In the VBA editor, double click on ThisWorkbook in the Project Window. Paste the code below into the code window and save. Close the workbook, then reopen. Your rows should be diminished. Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Change to actual sheet name lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A1:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JeffK" wrote in message ... I have a spreadsheet that will become quite large as time goes on so I want to use a Macro that will make a row height = 1 when a condition is met. Column A has 4 options in a drop down (In progress, Sold, Cancelled, Declined). when the file is opened, I would like the macro to adjust the rows height to 1 for those that are "Sold/Cancelled/Declined", leaving "In progress" and cells that are blank at height=15. The table will start at row 8. Thanks for your help. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro to adjust the row heights
Since it worked in my test set up, It should work for you. On the line
Set sh = ActiveSheet You will need to substitute the actual sheet name like: Set sh = Sheets("Somesheet") Because your workbook might not open to the sheet you want to adjust. Even if it does, there is no guarantee it will be active when the code runs, so use the sheet name. If it still does not run, note the error message and post back with that info. "JeffK" wrote in message ... I've applied the following but it doesn't seem to work, any thoughts? Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Broker lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A8:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JLGWhiz" wrote: In the VBA editor, double click on ThisWorkbook in the Project Window. Paste the code below into the code window and save. Close the workbook, then reopen. Your rows should be diminished. Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Change to actual sheet name lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A1:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JeffK" wrote in message ... I have a spreadsheet that will become quite large as time goes on so I want to use a Macro that will make a row height = 1 when a condition is met. Column A has 4 options in a drop down (In progress, Sold, Cancelled, Declined). when the file is opened, I would like the macro to adjust the rows height to 1 for those that are "Sold/Cancelled/Declined", leaving "In progress" and cells that are blank at height=15. The table will start at row 8. Thanks for your help. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro to adjust the row heights
Still doesn't work. No error message... nothing happens. This was created
on my work PC using Excel 2003 and I'm using 2007 at home (still saving as 97-2003 file). I haven't used 2007 version much, are there settings I have to adjust? "JLGWhiz" wrote: Since it worked in my test set up, It should work for you. On the line Set sh = ActiveSheet You will need to substitute the actual sheet name like: Set sh = Sheets("Somesheet") Because your workbook might not open to the sheet you want to adjust. Even if it does, there is no guarantee it will be active when the code runs, so use the sheet name. If it still does not run, note the error message and post back with that info. "JeffK" wrote in message ... I've applied the following but it doesn't seem to work, any thoughts? Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Broker lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A8:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JLGWhiz" wrote: In the VBA editor, double click on ThisWorkbook in the Project Window. Paste the code below into the code window and save. Close the workbook, then reopen. Your rows should be diminished. Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Change to actual sheet name lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A1:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JeffK" wrote in message ... I have a spreadsheet that will become quite large as time goes on so I want to use a Macro that will make a row height = 1 when a condition is met. Column A has 4 options in a drop down (In progress, Sold, Cancelled, Declined). when the file is opened, I would like the macro to adjust the rows height to 1 for those that are "Sold/Cancelled/Declined", leaving "In progress" and cells that are blank at height=15. The table will start at row 8. Thanks for your help. . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro to adjust the row heights
No Wait... It worked. I had the macro written in the wrong area.
When I open Alt+F11, it opens the code for the sheet but I had to write it under the workbook... If that makes any sense, I'm still new at this macro thing. Thanks for your help "JeffK" wrote: Still doesn't work. No error message... nothing happens. This was created on my work PC using Excel 2003 and I'm using 2007 at home (still saving as 97-2003 file). I haven't used 2007 version much, are there settings I have to adjust? "JLGWhiz" wrote: Since it worked in my test set up, It should work for you. On the line Set sh = ActiveSheet You will need to substitute the actual sheet name like: Set sh = Sheets("Somesheet") Because your workbook might not open to the sheet you want to adjust. Even if it does, there is no guarantee it will be active when the code runs, so use the sheet name. If it still does not run, note the error message and post back with that info. "JeffK" wrote in message ... I've applied the following but it doesn't seem to work, any thoughts? Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Broker lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A8:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JLGWhiz" wrote: In the VBA editor, double click on ThisWorkbook in the Project Window. Paste the code below into the code window and save. Close the workbook, then reopen. Your rows should be diminished. Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Change to actual sheet name lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A1:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JeffK" wrote in message ... I have a spreadsheet that will become quite large as time goes on so I want to use a Macro that will make a row height = 1 when a condition is met. Column A has 4 options in a drop down (In progress, Sold, Cancelled, Declined). when the file is opened, I would like the macro to adjust the rows height to 1 for those that are "Sold/Cancelled/Declined", leaving "In progress" and cells that are blank at height=15. The table will start at row 8. Thanks for your help. . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro to adjust the row heights
Go back and look at my first posting. I believe is says to do just that.
"JeffK" wrote in message ... No Wait... It worked. I had the macro written in the wrong area. When I open Alt+F11, it opens the code for the sheet but I had to write it under the workbook... If that makes any sense, I'm still new at this macro thing. Thanks for your help "JeffK" wrote: Still doesn't work. No error message... nothing happens. This was created on my work PC using Excel 2003 and I'm using 2007 at home (still saving as 97-2003 file). I haven't used 2007 version much, are there settings I have to adjust? "JLGWhiz" wrote: Since it worked in my test set up, It should work for you. On the line Set sh = ActiveSheet You will need to substitute the actual sheet name like: Set sh = Sheets("Somesheet") Because your workbook might not open to the sheet you want to adjust. Even if it does, there is no guarantee it will be active when the code runs, so use the sheet name. If it still does not run, note the error message and post back with that info. "JeffK" wrote in message ... I've applied the following but it doesn't seem to work, any thoughts? Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Broker lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A8:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JLGWhiz" wrote: In the VBA editor, double click on ThisWorkbook in the Project Window. Paste the code below into the code window and save. Close the workbook, then reopen. Your rows should be diminished. Private Sub Workbook_Open() Dim lr As Long, rng As Range, sh As Worksheet Set sh = ActiveSheet 'Change to actual sheet name lr = sh.Cells(Rows.Count, 1).End(xlUp).Row Set rng = sh.Range("A1:A" & lr) For Each c In rng If LCase(c) = "sold" Or LCase(c) = "cancelled" _ Or LCase(c) = "declined" Then Rows(c.Row).RowHeight = 1 End If Next End Sub "JeffK" wrote in message ... I have a spreadsheet that will become quite large as time goes on so I want to use a Macro that will make a row height = 1 when a condition is met. Column A has 4 options in a drop down (In progress, Sold, Cancelled, Declined). when the file is opened, I would like the macro to adjust the rows height to 1 for those that are "Sold/Cancelled/Declined", leaving "In progress" and cells that are blank at height=15. The table will start at row 8. Thanks for your help. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn off auto adjust of row heights | Excel Worksheet Functions | |||
Excel: copy grid, widths & heights down page: heights wrong! why? | Excel Discussion (Misc queries) | |||
creating formula to adjust per month | Excel Discussion (Misc queries) | |||
Creating a price list,with different row heights | Excel Discussion (Misc queries) | |||
Macro to Paste Special Row Heights | Excel Programming |