Code question

[Guest]

Does the worksheet have to active for this code to work?

Private Sub BtnDelete_Click()
Dim fRow As Long

On Error GoTo ender
fRow = Columns(1).Find(What:=TxtMan.Value, _
After:=Cells(5000, 1), LookIn:=xlFormulas, _
LookAT:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row
Rows(fRow).Delete
Exit Sub

ender:
MsgBox "Value not found"
End Sub

Because if it does not then it doesn't work. Right now it requires it to be
and I would like to knwo how to make so it doesn't need the sheet to be
active?

 

[JE McGimpsey]

One way:

Private Sub BtnDelete_Click()
Dim fRow As Long

On Error GoTo ender
With Sheets("Sheet1")
fRow = .Columns(1).Find( _
What:=TxtMan.Value, _
After:=.Cells(5000, 1), _
LookIn:=xlFormulas, _
LookAT:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Row
.Rows(fRow).Delete
End With
Exit Sub
ender:
MsgBox "Value not found"
End Sub

 

[Guest]

To make it not care what is active, use variables

Private Sub BtnDelete_Click()
Dim fRow As Long,WS as Worksheet, rngHitCell as Range
Set WS = Workbooks("Whatever").Worksheets("Whatever")

Set rngHitCell = WS.Columns(1).Find(What:=TxtMan.Value, _
After:=Cells(5000, 1), LookIn:=xlFormulas, _
LookAT:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)

If rngHitCell is Nothing Then
MsgBox "Value not found"
Else
WS.Rows(rngHitCell.Row).Delete
End If

End Sub

 

[Guest]

When in doubt you are best off to be explicit. Include the sheet and then you
will always hit the right sheet. Then there are no worries (so long as you
have not tried to select or activate something). Try something more like
this...

Private Sub BtnDelete_Click()
Dim fRow As Long

On Error GoTo ender
fRow = Sheets("Sheet1").Columns(1).Find(What:=TxtMan.Value, _
After:=Cells(5000, 1), LookIn:=xlFormulas, _
LookAT:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row
Rows(fRow).Delete
Exit Sub

ender:
MsgBox "Value not found"
End Sub

 

[Guest]

Actually, I forgot something ....

Private Sub BtnDelete_Click()
Dim fRow As Long,WS as Worksheet, rngHitCell as Range
Set WS = Workbooks("Whatever").Worksheets("Whatever")

Set rngHitCell = WS.Columns(1).Find(What:=TxtMan.Value, _
After:=Cells(5000, 1), LookIn:=xlFormulas, _
LookAT:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)

If rngHitCell is Nothing Then
MsgBox "Value not found"
Else
If rngHitCell.Row > 5000 then
WS.Rows(rngHitCell.Row).Delete
Else
Msgbox "Value found before Row 5000"
End If
End If

End Sub

 

[Guest]

Oops I missed one reference. JE's code does it. Or you could tighten things
up with this...

Private Sub BtnDelete_Click()

On Error GoTo ender
Sheets("Sheet1").Columns(1).Find(What:=TxtMan.Value, _
After:=Cells(5000, 1), LookIn:=xlFormulas, _
LookAT:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).EntireRow.Delete
Exit Sub

ender:
MsgBox "Value not found"
End Sub

 

[Guest]

Just wanted to caution you about this method. If you are sure the search will
never find the value in the 1st 5000 rows, then this is fine. However, if you
are specifying After because it may be, then you could delete a row above row
5000 using this method.

Another way to avoid this is to limit the search via
..Range(.Cells(5001,1),.Cells(65536,1)).Find( ....)

Bob