So it really was a group creation

That is the power of newsgroups Jim.


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jim Rech" wrote in message ...
But who knows.

I do!<g

It was one of those back and forth exchanges on Compuserve where we each
refined the previous version. So it really was a group creation that no
single person can take full credit for.

--
Jim
"Tom Ogilvy" wrote in message
...
| Here is a posting by John Green showing that method: (circa 1998)
|
| http://tinyurl.com/26yzr2
|
| Documented on a web site by One of the Early MVPs:
|
| http://www.beyondtechnology.com/geeks012.shtml
| Note the date of 1996 - 2007 at the bottom. Don't know when the page was
| created, but it was a long time ago.
|
| My understanding is it was developed by John Green/Jim Rech back when this
| forum was on Compuserve (circa 1995 or earlier). But who knows.
|
| --
| Regards,
| Tom Ogilvy
|
|
| "Jay" wrote in message
| ...
| Well, don't I feel silly. Thanks Ron.
|
| Jay
|
| "Ron de Bruin" wrote:
|
| See this page
| http://www.rondebruin.nl/last.htm
|
| --
|
| Regards Ron de Bruin
| http://www.rondebruin.nl/tips.htm
|
|
| "Jay" wrote in message
| ...
| I was looking for a function to do what Jim's does, but it didn't
quite
| meet
| my needs. I needed a function to find the last cell with data in
| specific
| rows/columns. I've modified Jim's function and I'm posting the
results
| for
| the next person that searches for the same thing.
|
| The formatting is broken, but it should fix itself if you past it
into
| an
| editor.
|
| Enjoy,
| Jay
|
| Public Function LastCellInRow(wks As Worksheet, RowNumber As Long) As
| Range
| '
|
================================================== ================================================== =
| ' Function: LastCellInRow
| ' Purpose: Search a specific row in a worksheet and find the last
cell
| in
| the row with data.
| ' Parameters: wks -- worksheet to check for data
| ' RowNumber -- the row to check for data
| ' Returns: A range representing the last cell in the row with data.
| ' Calls: none
| '
| ' Origin: Microsoft Office Online - Office Discussion Groups:
| microsoft.public.excel.programming
| '
|
[http://www.microsoft.com/office/comm...-27322d0ea6e8]
| '
| ' Author: Original code by: Jim Thomlinson Unknown
| ' Revised by: Jay 14-Mar-2007
| ' Last Revision: Jay 14-Mar-2007
| '
|
================================================== ================================================== =
| Dim lngLastColumn As Long ' last column
|
| ' starting from the last cell of the row, move leftward looking
for
| a
| cell with data
| lngLastColumn = wks.Cells.Find(What:="*", _
| After:=wks.Cells(RowNumber,
| Columns.Count), _
| Lookat:=xlPart, _
| LookIn:=xlFormulas, _
| SearchOrder:=xlByRows, _
| SearchDirection:=xlPrevious, _
| MatchCase:=False).Column
|
| ' if no data was found in the row
| If lngLastColumn = 0 Then
| ' set the column to 1
| lngLastColumn = 1
| End If
|
| ' set the return range of the cell
| Set LastCellInRow = wks.Cells(RowNumber, lngLastColumn)
|
| End Function ' == LastCellInRow ==
|
| Public Function LastCellInColumn(wks As Worksheet, ColumnNumber As
| Long) As
| Range
| '
|
================================================== ================================================== =
| ' Function: LastCellInColumn
| ' Purpose: Search a specific column in a worksheet and find the last
| cell in
| the column with data.
| ' Parameters: wks -- worksheet to check for data
| ' ColumnNumber -- the column to check for data
| ' Returns: A range representing the last cell in the column with
data.
| ' Calls: none
| '
| ' Origin: Microsoft Office Online - Office Discussion Groups:
| microsoft.public.excel.programming
| '
|
[http://www.microsoft.com/office/comm...-27322d0ea6e8]
| '
| ' Author: Original code by: Jim Thomlinson Unknown
| ' Revised by: Jay 14-Mar-2007
| ' Last Revision: Jay 14-Mar-2007
| '
|
================================================== ================================================== =
| Dim lngLastRow As Long ' last row
|
| ' starting from the last cell of the column, move upward looking
for
| a
| cell with data
| lngLastRow = wks.Cells.Find(What:="*", _
| After:=wks.Cells(Rows.Count,
| ColumnNumber), _
| Lookat:=xlPart, _
| LookIn:=xlFormulas, _
| SearchOrder:=xlByColumns, _
| SearchDirection:=xlPrevious, _
| MatchCase:=False).Row
|
| ' if no data was found in the column
| If lngLastRow = 0 Then
| ' set the row to 1
| lngLastRow = 1
| End If
|
| ' set the return range of the cell
| Set LastCellInColumn = wks.Cells(lngLastRow, ColumnNumber)
|
| End Function ' == LastCellInColumn ==
|
|
| "Jim Thomlinson" wrote:
|
| Here is a simple function that I use to get you the true last cell
in
| every
| instance. Guaranteed every time...
|
| Public Function LastCell(Optional ByVal wks As Worksheet) As Range
| Dim lngLastRow As Long
| Dim intLastColumn As Integer
|
| If wks Is Nothing Then Set wks = ActiveSheet
| On Error Resume Next
| lngLastRow = wks.Cells.Find(What:="*", _
| After:=wks.Range("A1"), _
| Lookat:=xlPart, _
| LookIn:=xlFormulas, _
| SearchOrder:=xlByRows, _
| SearchDirection:=xlPrevious, _
| MatchCase:=False).Row
| intLastColumn = wks.Cells.Find(What:="*", _
| After:=wks.Range("A1"), _
| Lookat:=xlPart, _
| LookIn:=xlFormulas, _
| SearchOrder:=xlByColumns, _
| SearchDirection:=xlPrevious, _
| MatchCase:=False).Column
| On Error GoTo 0
| If lngLastRow = 0 Then
| lngLastRow = 1
| intLastColumn = 1
| End If
| Set LastCell = wks.Cells(lngLastRow, intLastColumn)
|
| End Function
| --
| HTH...
|
| Jim Thomlinson
|
|
|
|