lastrow function help

[Matthew Dyer]

I've been using Chip's (i believe) lastrow function for quite a while now and just ran into something strange in Office 2010:

if i have data in several columns, and a header row, and one column only has two options - AA or AB. I sort data by that column alphabetically a-z -
if i run lastrow on that sheet with no filter i get the true lastrow
if i filter only ab i get the true lastrow
if i filter only aa i get 1 as the result of lastrow?

here's the function for those unfamiliar:

Function LastRow(sh As Worksheet)
On Error Resume Next
'only looks for visible data, will not return hidden rows
'assign value for lastrow by finding
'What:="*" |ANY value
'after:=sh.range("a1") |anything after the first cell on the sheet
'Lookat:=xlPart |Match any portion of the cell value
'LookIn:=xlValues |Look in cell values instead of formulas
'SearchOrder:=xlByRows |Search by Rows, change to xlByColumns to search by Columns
'SearchDirection:=xlprevious |Search End to Beginning
'MatchCase:=False |Not Upper/Lowercase specific
'.Row |Return the Row value of that cell, change to .Column to return Column Value
LastRow = sh.Cells.Find( _
What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False) _
.Row
On Error GoTo 0
End Function

 

[GS]

Hi Matthew,
This is interesting in that it just reaffirms my dislike for Excel's
Find() in general. Part of the reason for that is that in most
spreadsheet apps, Headers are placed in rows *below* the sheet's
headers, and which the Find() function has no way to handle what's data
and what's not. As a result I opted for using an array containing the
entire UsedRange wherein I can specify where data starts so I can
exclude the header rows from the search. The logic of this approach is
as follows...

I use Farpoint's Spread ActiveX spreadsheet control in my VB6 apps. It
has builtin sheet 'properties' for this...

DataColCnt, DataRowCnt

...which store a Long Integer value for the last col/row containing
data. The search includes all cells of the sheet regardless of how many
cols/rows the headers have because the header cols/rows are not
considered in the search. The beauty of this is that I can set how many
cols/rows the headers have and so don't have to compensate for extra
'non-data' areas. I just ask the sheet to tell me where the last data
item is located!

It's pretty common to use 'extra' non-data areas on a sheet and so I
made the following functions to compensate for Excel's lack of being
able to define neither of the number or text of col/row headers...


Public Function GetLastDataRow&(Wks As Worksheet, Optional StartRow&)
' Finds the last row of UsedRange that contains data.
' Allows for excluding any number of header rows.
'
' Args:
' Wks Object ref to the sheet being searched.
' StartRow Optional:
' A type Long that specifies where the search starts.
' If omitted then the entire range is searched.
'

Dim vData, n&, k&, lRow&

vData = Wks.UsedRange '//load the data
lRow = IIf(StartRow > 0, StartRow, 1) '//get 1st row

For n = UBound(vData) To lRow Step -1
For k = LBound(vData, 2) To UBound(vData, 2)
If Len(vData(n, k)) > 0 Then GetLastDataRow = n: Exit For
Next 'k
Next 'n
End Function

Public Function GetLastDataCol&(Wks As Worksheet, Optional StartCol&,
Optional StartRow&)
' Finds the last col of UsedRange that contains data.
' Allows for excluding any number of header rows/cols.
'
' Args:
' Wks Object ref to the sheet being searched.
' StartCol Optional:
' A type Long that specifies the start col of the search.
' If omitted the search starts at Col1.
' StartRow Optional:
' A type Long that specifies the start row of the search.
' If omitted the search starts at Row1.
'

Dim vData, n&, k&, lRow&, lCol&

vData = Wks.UsedRange '//load the data
lCol = IIf(StartCol > 0, StartCol, 1) '//get 1st col
lRow = IIf(StartRow > 0, StartRow, 1) '//get 1st row

For n = UBound(vData, 2) To lCol Step -1
For k = lRow To UBound(vData, 2)
If Len(vData(n, k)) > 0 Then GetLastDataCol = n: Exit For
Next 'k
Next 'n
End Function

...which I have not used very much in Excel since most of my users are
stepping away from M$ Office. I bought the Spread OCX back when M$
introduced the Office Ribbon because of so many users resisting to move
forward in the new (and strange at the time) UI environment. I provided
the old menubar and standard/formatting toolbars to several users to
help them through the transition to the new UI, allowing them to pretty
much do everything as before v2007 from the Addins tab of the Ribbon.

The point of this drivel is that working with both OCXs (an Excel
workbook is just a glorified ActiveX grid control on steroids!) enabled
me to offset some shortcomings of both! The Spread equivalent of
Excel's Find() is these 2 methods...

SearchCol(ByVal lCol As Long, ByVal lRowStart As Long, _
ByVal lRowEnd As Long, ByVal Text As String, _
ByVal SearchFlags As SearchFlagsConstants) As Long

SearchRow(ByVal lRow As Long, ByVal lColStart As Long, _
ByVal lColEnd As Long, ByVal Text As String, _
ByVal SearchFlags As SearchFlagsConstants) As Long

...where 'Text' is the format for all values in cells regardless of cell
'type'. That means the control defines data 'type' by the 'cell type'
as follows...

Button Cells
Check Box Cells
Combo Box Cells
Currency Cells
Custom Cells
Date Cells
Edit Cells (default type)
Number Cells
Owner Drawn Cells
Percent Cells
Mask (PIC) Cells
Picture Cells
Scientific Notation Cells
Static Text (Label) Cells
Time Cells

...so I can have a choice when exporting/importing data as to whether
it's formatted (typed).

Sorry about the 'long-in-tooth'! Let me know how (or if!) either of
these functions works with your data...

--
Garry

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

 

[GS]

Oops! I've never run the following col function and so I just now
noticed my copy/paste booboo on the inner loop. Here's the revised
(actually works) version...

Public Function GetLastDataCol&(Wks As Worksheet, Optional StartCol&,
Optional StartRow&)
' Finds the last col of UsedRange that contains data.
' Allows for excluding any number of header rows/cols.
'
' Args:
' Wks Object ref to the sheet being searched.
' StartCol Optional:
' A type Long that specifies the start col of the search.
' If omitted the search starts at Col1.
' StartRow Optional:
' A type Long that specifies the start row of the search.
' If omitted the search starts at Row1.
'

Dim vData, n&, k&, lRow&, lCol&

vData = Wks.UsedRange '//load the data
lCol = IIf(StartCol > 0, StartCol, 1) '//get 1st col
lRow = IIf(StartRow > 0, StartRow, 1) '//get 1st row

For n = UBound(vData, 2) To lCol Step -1
For k = lRow To UBound(vData)
If Len(vData(n, k)) > 0 Then GetLastDataCol = n: Exit For
Next 'k
Next 'n
End Function

I have used the row function, though, primarily to eliminate looping
the extra/blank rows included in UsedRange!

You might be interested in...
Spread defines ranges by cell positions in the sheet (grid) as
follows...

With fpSpread1
.Sheet = .ActiveSheet
.Row = <1st row#>
.Row2 = <last row#>
.Col = <1st col#>
.Col2 = <last col#>
End With

...and so these go with all the range functions/methods. Thus, I've
stopped using...

Dim lLastRow& lLastCol&

in favor of using...

Dim lRow2&, lCol2&

...to cut down on my typing difficulties due to having Lou Gehrig's.
This is also why I've adopted using VB's type symbols. The advantage is
my code's brevity and easier maintenance.

--
Garry

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

 

[GS]

I also just revised both functions as follows since trying the col
one...

Replace:
Exit For

with:
Exit Function

...since the col function has an inner loop. I didn't pick up on this
earlier because the data areas I tested had no blank fields (thus
contiguous data).

--
Garry

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

 

[GS]

Thought this might make for better code brevity...


Public Function GetLastDataPos&(Wks As Worksheet, _
IsRow As Boolean, 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

 

[GS]

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

 

[GS]

And finally.., why not...

Public Function GetLastDataPos&(Optional 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 Optional:
' Object ref to the sheet being searched.
' Defaults to ActiveSheet if missing.
'
' IsRow Optional:
' 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&

If Wks Is Nothing Then Set Wks = ActiveSheet

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