.StandardHeight property

[Brad E.]

Hi - I have a spreadsheet where a user can enter an integer from 1 to 9.
Depending on the entry, a WorksheetChange() event hides/displays the
immediate 9 rows below the entry.

Case "$A$3" 'Using Select Target.Address
X = Target.Value + 3
With ActiveSheet
For Y = 4 To 12
If Y <= X Then
.Rows(Y).RowHeight = .StandardHeight
Else
.Rows(Y).RowHeight = 0
End If
Next Y
End With 'End Select

Somehow, the .Standard Height is at zero and all 9 rows continually get set
to a height of 0. (.StandardHeight) is a read-only argument, so I am not
sure how this happened. Can anyone help me get this back to the 12.75 that
it should be?

 

[Gary Brown]

have you tried ...
X = Target.ROW + 3
???

 

[Rick Rothstein]

What size Standard Font do you show on the General Tab for the Tools/Options
dialog box (it controls the StandardHeight as far as I know)?

As for your code... I would not play with the RowHeight property; rather, I
would use the Hidden property for the Rows range. For example...

YourStartRow = 3
Rows(YourStartRow).Resize(9).Hidden = True

will hide Row 3 and the eight rows beneath it (for a total of nine hidden
rows). To show the rows, just assign False (instead of True) to the Rows
statement.

 

[JLGWhiz]

This syntax works for me:

..Rows.UseStandardHeight = True

It sets the applicable rows to the default height. In your case it would
be:

Rows(Y).UseStandardHeight = True

 

[Brad E.]

The ROW number won't help. I want to show the number of rows directly under
cell $A$3 depending on the user-entered VALUE into $A$3. Thanks for the
thought, though.
-- Brad E.

 

[Brad E.]

My Standard Font is Arial-10 and even after the reboot this morning, my rows
got hidden.

I have changed my coding to use the .Hidden feature, just like you
suggested. Now things are working great. Thanks a lot.
-- Brad E.

 

[Brad E.]

Thanks for the tip JLGWhiz. Your suggestion is more straight-forward than my
code. After getting Rick Rothstein's suggestion to work by hiding and
unhiding the rows, though, I am not going to try your suggestion. Besides,
if I am setting my ".RowHeight = .StandardHeight", I would guess that
".UseStandardHeight = True" would still give a height of zero.
-- Brad E.