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

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


geebee wrote:
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

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


"geebee" wrote:

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

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


"DS" wrote:

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


"geebee" wrote:

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

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

--
HTH...

Jim Thomlinson


"geebee" wrote:

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


"DS" wrote:

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


"geebee" wrote:

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

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.


"geebee" wrote:

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

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


Patricia Shannon wrote:
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.


"geebee" wrote:

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

Thank you very much

"Mark Driscol" wrote:

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


Patricia Shannon wrote:
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.


"geebee" wrote:

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

It worked perfectly. This fixed a long-standing problem in one of my VBA
macros.

"Mark Driscol" wrote:

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


Patricia Shannon wrote:
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.


"geebee" wrote:

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

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


"Patricia Shannon" wrote:

It worked perfectly. This fixed a long-standing problem in one of my VBA
macros.

"Mark Driscol" wrote:

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


Patricia Shannon wrote:
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.


"geebee" wrote:

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

Thanks. This is good to have.

"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


"Patricia Shannon" wrote:

It worked perfectly. This fixed a long-standing problem in one of my VBA
macros.

"Mark Driscol" wrote:

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


Patricia Shannon wrote:
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.


"geebee" wrote:

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

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

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

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

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

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
|
|
|
|

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
|
|
|
|