Hi all hope your all well
I have this macro that i use to find the activecell value down a column I can use it in any column. The problem is if the data in the column is 2000 rows sometimes even more rows. Example: Row 100 Activecell value is Tom, find down Tom is in row 500 find again Tom is in row 1500 now there is no more Toms in the last 500 rows I don't know that so if I find down again the macro can take a long time to run maybe because it searching a million rows. How can I get the code to stop the search at the last value in the column at 2000 or more.
Sub FindDown_ThenStop()
Dim lngRow As Long
For lngRow = ActiveCell.Row + 1 To ActiveSheet.UsedRange.Row _
+ ActiveSheet.UsedRange.Rows.Count - 1
If Cells(lngRow, ActiveCell.Column).value = ActiveCell.value Then
On Error GoTo fin
Cells(lngRow, ActiveCell.Column).Select
Exit Sub
End If
Next
fin:
End Sub
Thanks in advance
Graham
I have this macro that i use to find the activecell value down a column I can use it in any column. The problem is if the data in the column is 2000 rows sometimes even more rows. Example: Row 100 Activecell value is Tom, find down Tom is in row 500 find again Tom is in row 1500 now there is no more Toms in the last 500 rows I don't know that so if I find down again the macro can take a long time to run maybe because it searching a million rows. How can I get the code to stop the search at the last value in the column at 2000 or more.
Sub FindDown_ThenStop()
Dim lngRow As Long
For lngRow = ActiveCell.Row + 1 To ActiveSheet.UsedRange.Row _
+ ActiveSheet.UsedRange.Rows.Count - 1
If Cells(lngRow, ActiveCell.Column).value = ActiveCell.value Then
On Error GoTo fin
Cells(lngRow, ActiveCell.Column).Select
Exit Sub
End If
Next
fin:
End Sub
Thanks in advance
Graham