I have found that UsedRange is a very unreliable method of finding the last
row. If the cells below the last data cells are formatted then usedrange can
include them even though they have no data. Also, if you enter data in any
cells below the normal data range and then clear those cells then usedrange
can include the cleared cells.

--
Regards,

OssieMac


"Subodh" wrote:

On Mar 17, 8:51 am, Rob wrote:
Hi,

The code below checks data in a row and depending on the outcome will
display a User Form. If the data in the row does not meet the criteria to
show the User Form then a cell in the row below is activated. If the User
Form is displayed (because the data has met the criteria) then, on closing it
as previously mentioned a cell in the row below is activated.

Sub CheckVolumeRise()
If ActiveCell <ActiveCell.Offset(0, 2) And _
ActiveCell.Offset(0, 2) < ActiveCell.Offset(0, 4) Then
Selection.End(xlToLeft).Select
CriteriaReached.Show
Selection.End(xlToRight).Select
ActiveCell.Offset(1, -4).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
End Sub

Please will someone show me how to loop this code down through the rows
until an empty row is reached.

Thank you.
I have modified the code as follows
Try this out.
Sub Chkrise()
Dim nosofrows As Long
start:
If ActiveCell < ActiveCell.Offset(0, 2) And _
ActiveCell.Offset(0, 2) < ActiveCell.Offset(0, 4) Then
Selection.End(xlToLeft).Select
criteriaReached.Show
'Selection.End(xlToRight).Select (not considered necessary so omitted)
'ActiveCell.Offset(1, -4).Activate (not considered necessary so
omitted)
Else
ActiveCell.Offset(1, 0).Activate
nosofrows = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Row - 1 'this gives the last rows that is used
If ActiveCell.Row < nosofrows Then GoTo start
End If
'ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell ).Activate

End Sub
.