go to first empty row

G

Guest

hi,

I am writing code to copy data from another sheet and paste it into the
first new empty row in another worksheet. The problem is I dont know how to
go to the first available empty row in the new worksheet, to avoid
overwriting data that is already there.

Can someone help me with this?

Thanks in advance,
geebee
 
M

Mark Driscol

The code

Range("A65536").End(xlUp).Offset(1, 0).Address

will give you the address of the first cell in Column A that is blank.
If there are no blank rows between rows that contain data, this may
give you what you need. If you want to check to see if the entire row
is blank, that is slightly more involved. Will something like the
above work for your purposes?

Mark
 
G

Guest

Hi geebee,

There's a bunch of ways to do this. The one I generally use is:

Range("A65536").End(xlUp).Offset(1,0).Select

This will take you to the first empty Column A (not including any blank rows
amongst the data) - just change the column name to whavever you need.

HTH
DS
 
G

Guest

hi,

thanks.. worked. Now I just gotta get the previously selected values copied
from the other workbook pasted into the newly selected row. But I am not
sure how?

Here is the copy code:

Range(Range("A2"), Range("O2").End(xlDown)).Select
Range(Range("A2"), Range("O2").End(xlDown)).Copy

Thanks in advance,
geebee
 
G

Guest

Assuming you are not trying to paste special then you want something like
this...

with activesheet
.Range(.Range("A2"), .Range("O2").End(xlDown)).Copy _
Worksheet("Sheet2").cells(rows.count, "A").end(xlup).offset(1,0)
end with
 
G

Guest

You can find the last cell as follows:
arange.SpecialCells(xlLastCell)
where arange is a range in the desired spreadsheet
eg.
ActiveCell.SpecialCells(xlLastCell)
or range("A1").SpecialCells(xlLastCell)

(In case you lose this msg, you can get the code to go to the cell that is
in the last used row and column by recording a macro where you do Ctrl+End.)

This is the cell at the intersection of the last row used and last column
used.
This cell itself might or might not be blank.

Then you can use the offset as shown by others to move down a row.

One problem to be aware of is that if you delete the contents of cells by
using the Delete or Backspace or Ctrl+X keys, you only delete the contents.
The cells will still be part of the spreadsheet. So then the last cell would
not be what you want.

You can fix this by deleting the rows and columns by using
Menu Edit/Delete
In this case, the last cell will still not be reset until you do a Save.
Sometimes a SaveAs to itself is needed.
 
M

Mark Driscol

Or, you can execute the statement

i = ActiveSheet.UsedRange.Rows.Count

to reset the row count after rows are deleted, and then use
SpecialCells(xlLastCell). John Walkenbach lists this tip on his
website.


Mark
 
G

Guest

Thank you very much

Mark Driscol said:
Or, you can execute the statement

i = ActiveSheet.UsedRange.Rows.Count

to reset the row count after rows are deleted, and then use
SpecialCells(xlLastCell). John Walkenbach lists this tip on his
website.


Mark
Click to expand...
 
G

Guest

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
 
G

Guest

Thanks. This is good to have.

Jim Thomlinson said:
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
Click to expand...
 
G

Guest

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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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 ==
 
R

Ron de Bruin

See this page
http://www.rondebruin.nl/last.htm

--

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


Jay said:
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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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 said:
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
Click to expand...
Click to expand...
 
G

Guest

Well, don't I feel silly. Thanks Ron.

Jay

Ron de Bruin said:
See this page
http://www.rondebruin.nl/last.htm

--

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


Jay said:
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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
Well, don't I feel silly. Thanks Ron.

Jay

Ron de Bruin said:
See this page
http://www.rondebruin.nl/last.htm

--

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


Jay said:
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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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 ==


:

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
Click to expand...
Click to expand...
Click to expand...
 
J

Jim Rech

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
| 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
|
|
| | > 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
| >>
| >>
| >> | >> >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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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
| >>
| >>
|
|
 
R

Ron de Bruin

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 said:
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
| 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
|
|
| | > 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
| >>
| >>
| >> | >> >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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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/com...ming&mid=78b06a65-cdfd-4db1-bf59-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
| >>
| >>
|
|
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy row to first empty row in new sheet 3
Help finding first empty cell in column 1
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
OFFSET LOOKUP function in VBA? 9
Next Blank Row 11
Search for first empty cell in column 3
VBA comparing sheets and cells 1
Search for empty cells and move cells next to 10

Top