View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Juan[_2_] Juan[_2_] is offline
external usenet poster
 
Posts: 1
Default Remove rows based on any selected criteria

I would to remove rows containing any data type (text,
number or date) that the user choose. i create the form
where the range and the criteria can be entered, however
in the find portion if I change find(what:"0" ... it works
fine but when I change for NewTxtRem (see code below) that
comes from the user form caption TextToRemove I get
runtime error 438 Object doesn't support this property or
method in the line
Set rgFoundCell = RefEdit2.Find(What:=NewTxtRem,
LookAt:=xlWhole)

Can I get some help to how overcome this? THANKS A LOT

Private Sub go_Click()
' Perform the selected operation
Dim WorkRange As Range
Dim NewTxtRem As Variant
NewTxtRem = TextToRemove.Text

' Validate Range reference
If Not ValidReference(RefEdit2.Text) Then
MsgBox "Invalid range.", vbInformation
Application.ScreenUpdating = True
With RefEdit2
.SelStart = 0
.SelLength = 100
.SetFocus
End With
Exit Sub
End If

' Figure out what to do
Application.ScreenUpdating = False
Dim rgFoundCell As Range
Application.ScreenUpdating = False
Set rgFoundCell = RefEdit2.Find
(What:=NewTxtRem, LookAt:=xlWhole)
Do Until rgFoundCell Is Nothing
rgFoundCell.EntireRow.Delete
Set rgFoundCell = NewTxtRem.FindNext
Loop
Application.ScreenUpdating = True
Unload Me
End Sub