Find Text or Part Of Text
 

Hi there,

Could someone tell me the VBA code to bring either bring up the FIND menu or
an input box for someone to type in a word or part of a word so a list comes
up of the cell reference.

Many thanks.

Kind Regards
Kevin

Find Text or Part Of Text
 

Tools / Macros / Record Macro - Edit / Find - Stop recording - Examine code

Example

Sub DeleteRowsContaining()
Dim R As Long
Dim Ans As String
Dim c As Range
Dim lrow As Long

Ans = InputBox("What string do you want rows to be deleted if they
contain it?")
Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For R = lrow To 1 Step -1
With Cells(R, 1)
Set c = .find(Ans, LookIn:=xlValues)
If Not c Is Nothing Then
.EntireRow.Delete
End If
End With
Next R
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"kevin" wrote in message
...
Hi there,

Could someone tell me the VBA code to bring either bring up the FIND menu
or
an input box for someone to type in a word or part of a word so a list
comes
up of the cell reference.

Many thanks.

Kind Regards
Kevin

Find Text or Part Of Text
 

Hi Ken,

Thanks but I just want to find the text not delete it. Can you help.

many thanks.

kevin.

"Ken Wright" wrote:

Tools / Macros / Record Macro - Edit / Find - Stop recording - Examine code

Example

Sub DeleteRowsContaining()
Dim R As Long
Dim Ans As String
Dim c As Range
Dim lrow As Long

Ans = InputBox("What string do you want rows to be deleted if they
contain it?")
Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For R = lrow To 1 Step -1
With Cells(R, 1)
Set c = .find(Ans, LookIn:=xlValues)
If Not c Is Nothing Then
.EntireRow.Delete
End If
End With
Next R
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"kevin" wrote in message
...
Hi there,

Could someone tell me the VBA code to bring either bring up the FIND menu
or
an input box for someone to type in a word or part of a word so a list
comes
up of the cell reference.

Many thanks.

Kind Regards
Kevin