Conditional row height

[Mike Storms]

I'm familiar with VBA programming in Access, but not familiar with Excel syntax. How would I set RowHeight=0 for each row in a selected range, conditional on the value of a specific cell in the row? Also, the range has been selected by the user, so the code first needs to detect the dimensions (first and last row) of the range.

 

[joeu2004]

I'm familiar with VBA programming in Access, but not
familiar with Excel syntax. How would I set RowHeight=0
for each row in a selected range, conditional on the
value of a specific cell in the row?

Although you could do it that way, it is more reliable to use the Hidden
property. That way, if you want unhide the row programmatically later, you
do not to remember the previous row height in order to restore it.

Also, the range has been selected by the user, so the
code first needs to detect the dimensions (first and
last row) of the range.

Selection.EntireRow.Hidden = True

PS: The Record Macro feature is a great way to learn how to do things in
Excel VBA. (Caveat: But often, you can greatly simply the recorded code.)

 

[GS]

Mike Storms formulated the question :

I'm familiar with VBA programming in Access, but not familiar with Excel
syntax. How would I set RowHeight=0 for each row in a selected range,
conditional on the value of a specific cell in the row? Also, the range has
been selected by the user, so the code first needs to detect the dimensions
(first and last row) of the range.

Firstly, VBA syntax is the same for all apps that use it. The variable
lies with the object library of the app you write code foe.

Secondly, the range is already dimensioned if you use 'Selection'.

lTotalRows = Selection.Rows.Count
lFirstRow = Selection.Rows(1).Row
lLastRow = Selection.Rows(lTotalRows).Row

Loop the Selection row by row and use WorksheetFunction.CountIf to see
if the target cell contains the criteria <value>...

Dim r As Long
For r = 1 To Selection.Rows.Count
If WorksheetFunction.CountIf(Selection.Rows(r), <value>) > 0 Then _
Rows(r).RowHeight = 0 '(or: Rows(r).Hidden = True)
Next 'r

...where there's no chance another cell in the row will contain the
criteria value.

Alternatively, you can check a specific cell as follows...

Dim r As Long
For r = 1 To Selection.Rows.Count
If Cells(r, "ColLabel") = <value> Then _
Rows(r).RowHeight = 0 '(or: Rows(r).Hidden = True)
Next 'r

...where you need to substitute "ColLabel" with the actual column's
label where the value to be checked is located.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[joeu2004]

I'm familiar with VBA programming in Access, but not
familiar with Excel syntax. How would I set RowHeight=0
for each row in a selected range, conditional on the
value of a specific cell in the row? [....]
Also, the range has been selected by the user, so the
code first needs to detect the dimensions (first and
last row) of the range.

Selection.EntireRow.Hidden = True

I misinterpreted your intent when the user selects multiple rows. You did
says "for each row". And I neglected to comment on the "conditional" part.

You might write:

Sub doit()
Const mycol As Long = 4 ' column number of the "specific cell"
Const myval = 3 ' conditional value
Dim nr As Long, i As Long, rng As Range
Application.ScreenUpdating = False
nr = Selection.Rows.Count
' usually faster to process rows in reverse when deleting or hiding them
For i = nr To 1 Step -1
Set rng = Selection.Cells(i, 1).EntireRow
If rng.Cells(1, mycol) = myval Then rng.Hidden = True
Next
Application.ScreenUpdating = True
End Sub

 

[Mike Storms]

Perfect. Such an elegant little snippet. Worked like a charm with a minimum number of lines. Thanks much.