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.

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.

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

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.


.

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.


.

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.


.



.

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.


.



.

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.


.



.