Given the most common usage will be to find where to put new rows of
data, I revised the function header as follows to further reduce typing
in the caller...
Public Function GetLastDataPos&(Wks As Worksheet, _
Optional IsRow As Boolean = True, Optional StartPos& = 1)
' Finds the last row or col of UsedRange that contains data.
' Allows for excluding any number of header rows/cols.
'
' Args:
' Wks Object ref to the sheet being searched.
'
' IsRow Boolean value that determines which axis to test.
'
' StartPos Optional:
' A type Long that specifies the start row/col of the
search.
' If omitted the search starts at A1.
'
Dim n&, k&, lLast&
With Wks.UsedRange
lLast = IIf(IsRow, .Rows.Count, .Columns.Count)
For n = lLast To StartPos Step -1
k = Application.CountA(IIf(IsRow, Wks.Rows(n), Wks.Columns(n)))
If k 0 Then GetLastDataPos = n: Exit Function
Next 'n
End With 'Wks.UsedRange
End Function
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion