ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Code question (https://www.excelbanter.com/excel-programming/400356-code-question.html)

Mekinnik
Code question
 
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
Code question
 
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


In article ,
Mekinnik wrote:

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?

INTP56
Code question
 
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

"Mekinnik" wrote:

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?

Jim Thomlinson
Code question
 
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.Valu e, _
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
--
HTH...

Jim Thomlinson


"Mekinnik" wrote:

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?

INTP56
Code question
 
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

"INTP56" wrote:

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

"Mekinnik" wrote:

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?

Jim Thomlinson
Code question
 
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.Valu e, _
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
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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.Valu e, _
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
--
HTH...

Jim Thomlinson


"Mekinnik" wrote:

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?

INTP56
Code question
 
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


"Jim Thomlinson" wrote:

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.Valu e, _
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
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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.Valu e, _
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
--
HTH...

Jim Thomlinson


"Mekinnik" wrote:

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?


All times are GMT +1. The time now is 06:10 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com