Determine rowheight

T

The Cube

Hi all

I am looking for a function, XL97+ compatible, that will return the
rowheight of the cell that calls the function. ie similar to
=CELL("Width")
except behaving like
=CELL("Height")
which of course doesn't work.

Ultimately, the intention is to be able to determine the reference of the
first visible row in an autofiltered range, independent of the filter
criteria.

My plan is to reserve a column for the rowheight, and another for the
cumulative totals of all rowheights. The first rowheight greater than zero
would then be the first visible row.

If there is a better way then I should be grateful to learn it, although I
would still like the rowheight function as that may be useful elsewhere.

Thanks

-Cube
 
T

Thomas

Nothing built for that in so use a UDF in a module
Public Function rHeight(r As Range)
rHeight = r.RowHeight
End Function
Then in your worksheet cells
= rHeight(A1)
 
T

The Cube

Oh. I have found a VBA solution to this. Can I take it that there is no
non-VBA solution?

-Cube
 
T

The Cube

Please Ignore - Thomas responded

-Cube

The Cube said:
Oh. I have found a VBA solution to this. Can I take it that there is no
non-VBA solution?

-Cube
Click to expand...
 
D

Dave Peterson

Instead of looking at the rowheight, you could just find that row itself:

Option Explicit
Sub testme02()

Dim rngF As Range

With Worksheets("Sheet1")
With .AutoFilter.Range.Columns(1)
On Error Resume Next
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
MsgBox "no visible cells--save the header"
Else
MsgBox rngF.Cells(1).Row
End If
End With
End With
End Sub
 
T

The Cube

Dave Peterson said:
Instead of looking at the rowheight, you could just find that row itself:

Option Explicit
Sub testme02()

Dim rngF As Range

With Worksheets("Sheet1")
With .AutoFilter.Range.Columns(1)
On Error Resume Next
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
MsgBox "no visible cells--save the header"
Else
MsgBox rngF.Cells(1).Row
End If
End With
End With
End Sub
Click to expand...

Thanks Dave. I chopped it about a bit to get a UDF but couldn't get it to
work:

Function testme02(L As Long)
Application.Volatile
Dim rngF As Range

With Worksheets("Analysis")
With .AutoFilter.Range.Columns(1)
On Error Resume Next
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
L = 0
Else
L =rngF.Cells(1).Row
End If
End With
End With
End Sub


This always seems to return #VALUE! in a cell with a formula =testme02()

Any idea what is wrong, from the limited info available?

-Cube

<Original post snipped>
 
T

The Cube

oh silly me. Earlier I wrote
Thanks Dave. I chopped it about a bit to get a UDF but couldn't get it to
work:

Function testme02(L As Long)
Application.Volatile
Dim rngF As Range

With Worksheets("Analysis")
With .AutoFilter.Range.Columns(1)
On Error Resume Next
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
L = 0
Else
L =rngF.Cells(1).Row
End If
End With
End With
End Sub


This always seems to return #VALUE! in a cell with a formula =testme02()

Any idea what is wrong, from the limited info available?

-Cube

<Original post snipped>
Click to expand...

Should of course be

Function testme02() As Long
Application.Volatile
Dim rngF As Range

With Worksheets("Analysis")
With .AutoFilter.Range.Columns(1)
On Error Resume Next
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
testme02 = 0
Else
testme02 =rngF.Cells(1).Row
End If
End With
End With
End Sub

Now it works. Thanks Dave.

-Cube
 
J

J.E. McGimpsey

For one thing, you're not returning anything from the function. To
do that, you set the function = the return value. Also, at least for
some versions of XL, SpecialCells(xlCellTypeVisible) doesn't work
when called from the worksheet - it returns the entire range,
regardless of the Autofilter.

You also should get a compile error with End Sub rather than End
Function.

This worked for me:

Public Function testme02() As Long
Application.Volatile
Dim rngF As Range
Dim cell As Range

testme02 = 0
With Worksheets("Analysis")
With .AutoFilter.Range.Columns(1)
For Each cell In .Offset(1, 0).Resize( _
.Rows.Count - 1).Cells
If Not cell.EntireRow.Hidden Then
testme02 = cell.Row
Exit For
End If
Next cell
On Error GoTo 0
End With
End With
End Function
 
T

The Cube

Yes, the "End Sub" was a typo in transcription. Thanks for the other
pointers.

-Cube
 
D

Dave Peterson

Glad you got it to work. (I've had the same problem that J.E. wrote about with
specialcells in a function called from a worksheet.)

(I incorrectly thought that you wouldn't be using it from a worksheet function.)
 
Top