I have code used to append new records (rows) on sheet 4. In the past, I use
the lastrow procedure (below, from this newsgroup) to find the last row
where I can add new data. however, in this particular case, it keeps trying
to add my data near the top of the worksheet. So I used the rows.count to
start troubleshooting, and got the correct value there even though the
lastrow procedure is still at the top of the sheet. I have saved, closed,
and re-opened the workbook, to no avail.

Any ideas why these two would return different values?

Thanks,
Keith
XP/XL2003

(misc code deleted)

My_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
MsgBox Sheet4.UsedRange.Rows.Count
'returns a value of 77, which is accurate
MsgBox My_LastRow
'returns a value of 2, then after adding a record it returns a value
of 3.

use this and see how it works, change the "A" if that is not where the data is
My_LastRow=sheets(4).cells(rows.count,"A").end(xlu p).row

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Keith R" wrote:


I have code used to append new records (rows) on sheet 4. In the past, I use
the lastrow procedure (below, from this newsgroup) to find the last row
where I can add new data. however, in this particular case, it keeps trying
to add my data near the top of the worksheet. So I used the rows.count to
start troubleshooting, and got the correct value there even though the
lastrow procedure is still at the top of the sheet. I have saved, closed,
and re-opened the workbook, to no avail.

Any ideas why these two would return different values?

Thanks,
Keith
XP/XL2003

(misc code deleted)

My_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
MsgBox Sheet4.UsedRange.Rows.Count
'returns a value of 77, which is accurate
MsgBox My_LastRow
'returns a value of 2, then after adding a record it returns a value
of 3.

I was testing some new code, and identified that the LastRow procedure is
accurately identifying the last used row /only/ for the last column in my
dataset (Column U, where I just added code to add the date the record was
appended from my userform). All of the old records (rows 3-77) have
guaranteed data in columns A and B, more than half have data in C, and then
data is sporadic in G through S. Cells in G through S that have data also
automatically have comments on those cells.

Any advice or suggestions still welcome and appreciated!
Keith


"Keith R" wrote in message
...

I have code used to append new records (rows) on sheet 4. In the past, I
use the lastrow procedure (below, from this newsgroup) to find the last
row where I can add new data. however, in this particular case, it keeps
trying to add my data near the top of the worksheet. So I used the
rows.count to start troubleshooting, and got the correct value there even
though the lastrow procedure is still at the top of the sheet. I have
saved, closed, and re-opened the workbook, to no avail.

Any ideas why these two would return different values?

Thanks,
Keith
XP/XL2003

(misc code deleted)

My_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
MsgBox Sheet4.UsedRange.Rows.Count
'returns a value of 77, which is accurate
MsgBox My_LastRow
'returns a value of 2, then after adding a record it returns a
value of 3.

Here is the function that I use to get the bottom right most cell on a given
sheet.

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

You can use it like this...
My_LastRow = lastcell(sheet4).row
--
HTH...

Jim Thomlinson


"Keith R" wrote:


I have code used to append new records (rows) on sheet 4. In the past, I use
the lastrow procedure (below, from this newsgroup) to find the last row
where I can add new data. however, in this particular case, it keeps trying
to add my data near the top of the worksheet. So I used the rows.count to
start troubleshooting, and got the correct value there even though the
lastrow procedure is still at the top of the sheet. I have saved, closed,
and re-opened the workbook, to no avail.

Any ideas why these two would return different values?

Thanks,
Keith
XP/XL2003

(misc code deleted)

My_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
MsgBox Sheet4.UsedRange.Rows.Count
'returns a value of 77, which is accurate
MsgBox My_LastRow
'returns a value of 2, then after adding a record it returns a value
of 3.

You have not specified all of the arguments to the Find method, which might
be causing some of the problem. This can be very troublesome, as a macro
will work for a while, then quit working when a user uses the Find feature
from the user interface and changes some of the options.

From the topic "Find Method" in Visual Basic Help:

"The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each
time you use this method. If you don’t specify values for these arguments
the next time you call the method, the saved values are used. Setting these
arguments changes the settings in the Find dialog box, and changing the
settings in the Find dialog box changes the saved values that are used if
you omit the arguments. To avoid problems, set these arguments explicitly
each time you use this method."

If you insist on using the Find method in VBA code, I would ALWAYS set ALL
of the above mentioned 4 arguments!

In this situation with variable length blocks of data, I generally use the
UsedRange property and then use code something like the following. (Note
that the .Row property is the TOP row of data in rngUsedRange, and
..Rows.Count is the number of rows of data in rngUsedRange. Hence the reason
that you add both of them together to locate the next available row below
the block of data.)

Public Sub Demo()
Dim rngUsedRange As Range
Dim lngNextRow As Long

Set rngUsedRange = Sheet4.UsedRange

With rngUsedRange
lngNextRow = .Row + .Rows.Count 'In case of blank rows above data.
End With

'More code to add new data.
End Sub

--
Regards,
Bill Renaud

Put the function in your standard module 1. Then use it as shown
in the sample Sub beneath the function.

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Sub xyz()
x = LastRow(Sheets(1))
MsgBox x
End Sub


"Jim Thomlinson" wrote:

Here is the function that I use to get the bottom right most cell on a given
sheet.

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

You can use it like this...
My_LastRow = lastcell(sheet4).row
--
HTH...

Jim Thomlinson


"Keith R" wrote:


I have code used to append new records (rows) on sheet 4. In the past, I use
the lastrow procedure (below, from this newsgroup) to find the last row
where I can add new data. however, in this particular case, it keeps trying
to add my data near the top of the worksheet. So I used the rows.count to
start troubleshooting, and got the correct value there even though the
lastrow procedure is still at the top of the sheet. I have saved, closed,
and re-opened the workbook, to no avail.

Any ideas why these two would return different values?

Thanks,
Keith
XP/XL2003

(misc code deleted)

My_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
MsgBox Sheet4.UsedRange.Rows.Count
'returns a value of 77, which is accurate
MsgBox My_LastRow
'returns a value of 2, then after adding a record it returns a value
of 3.

I just discovered that using the UsedRange property (in Excel 2000) causes
Excel to "clean up" any cells below and to the right of the UsedRange that
used to have data in them. In other words, after calling UsedRange,
Ctrl+End will take you to the cell at the intersection of the last row and
last column of actual data, not to where the last cell of data used to be.
It used to be that you had to save the workbook for this "clean up" to be
done.

--
Regards,
Bill Renaud

Thanks to all (John, Jim, Bill, and JLG) for the quick response. I've
adapted some of the code provided, and will try to understand the Find
statement a little better as well- I've used the LastRow procedure without
problem for years, and until I fully understand the conditions that resulted
in the incorrect value, I won't know how many of my workbooks from the past
few years are at risk. <sinking feeling

Thanks for the help though, it looks like this project can move forward.

Best,
Keith

"John Bundy" (remove) wrote in message
...
use this and see how it works, change the "A" if that is not where the
data is
My_LastRow=sheets(4).cells(rows.count,"A").end(xlu p).row

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Keith R" wrote:


I have code used to append new records (rows) on sheet 4. In the past, I
use
the lastrow procedure (below, from this newsgroup) to find the last row
where I can add new data. however, in this particular case, it keeps
trying
to add my data near the top of the worksheet. So I used the rows.count to
start troubleshooting, and got the correct value there even though the
lastrow procedure is still at the top of the sheet. I have saved, closed,
and re-opened the workbook, to no avail.

Any ideas why these two would return different values?

Thanks,
Keith
XP/XL2003

(misc code deleted)

My_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
MsgBox Sheet4.UsedRange.Rows.Count
'returns a value of 77, which is accurate
MsgBox My_LastRow
'returns a value of 2, then after adding a record it returns a
value
of 3.