Creating a Macro to adjust the row heights

[JeffK]

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.

 

[JLGWhiz]

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

 

[Dave Peterson]

Check your other post.

But use a custom filter to show both the "in progress" or blanks.

 

[JeffK]

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]

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]

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?

 

[JeffK]

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

 

[JLGWhiz]

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