match data
 

am trying to get the following code to look up a name entered in the text box
, then return the value of th row number as X . rather than users having to
enter the row number (have tried using the match function but cant seem to
get it to work in vb),

thanks in advance

Sub DeleteRequestedRowsInAllSheets()
Dim sh As Worksheet

x = InputBox("Please Enter The Line Number Of The Person To Delete From This
Record")

Dim n As Single

For Each sh In Worksheets(Array("Jan", "Feb", "March", "april", "may",
"June", "July", "Aug", "Sep", "Oct", "Nov", "Dec", "Overview"))
sh.Rows(x).delete
Next sh

End Sub

match data
 

Rich:

try,

Sub DeleteRequestedRowsInAllSheets()
Dim sh As Worksheet
Dim c As Range, rng As Range
x = InputBox("Please Enter The Keyword To Delete From This Record ")
Dim n As Single
For Each sh In Worksheets(Array("Jan", "Feb", "March", "april", "may",
"June", "July", "Aug", "Sep", "Oct", "Nov", "Dec", "Overview"))
sh.Activate
With sh.UsedRange
Set c = .Find(x, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If rng Is Nothing Then
Set rng = c
Else
Set rng = Application.Union(rng, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
rng.EntireRow.Delete
Set rng = Nothing
End With
Next sh
End Sub

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Rich" wrote:

am trying to get the following code to look up a name entered in the text box
, then return the value of th row number as X . rather than users having to
enter the row number (have tried using the match function but cant seem to
get it to work in vb),

thanks in advance

Sub DeleteRequestedRowsInAllSheets()
Dim sh As Worksheet

x = InputBox("Please Enter The Line Number Of The Person To Delete From This
Record")

Dim n As Single

For Each sh In Worksheets(Array("Jan", "Feb", "March", "april", "may",
"June", "July", "Aug", "Sep", "Oct", "Nov", "Dec", "Overview"))
sh.Rows(x).delete
Next sh

End Sub