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.

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" <(E-Mail Removed)> wrote in message
news:9250DAB0-7559-4713-91E8-(E-Mail Removed)...
>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.

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

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" <(E-Mail Removed)> wrote in message
> news:9250DAB0-7559-4713-91E8-(E-Mail Removed)...
> >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.
>
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:2DD220C6-DF92-4B6D-BB75-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:9250DAB0-7559-4713-91E8-(E-Mail Removed)...
>> >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.
>>
>>
>> .
>>

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" <(E-Mail Removed)> wrote in message
> news:2DD220C6-DF92-4B6D-BB75-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:9250DAB0-7559-4713-91E8-(E-Mail Removed)...
> >> >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.
> >>
> >>
> >> .
> >>
>
>
> .
>

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" <(E-Mail Removed)> wrote in message
> > news:2DD220C6-DF92-4B6D-BB75-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > >> news:9250DAB0-7559-4713-91E8-(E-Mail Removed)...
> > >> >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.
> > >>
> > >>
> > >> .
> > >>
> >
> >
> > .
> >

Go back and look at my first posting. I believe is says to do just that.



"JeffK" <(E-Mail Removed)> wrote in message
news:56F19AC9-F985-482F-976E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> > news:2DD220C6-DF92-4B6D-BB75-(E-Mail Removed)...
>> > > 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" <(E-Mail Removed)> wrote in message
>> > >> news:9250DAB0-7559-4713-91E8-(E-Mail Removed)...
>> > >> >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.
>> > >>
>> > >>
>> > >> .
>> > >>
>> >
>> >
>> > .
>> >