[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 439

# Excel sheet find for a text and delete the whole row

Hi,

I want to find for a text and delete the whole row in the excel sheet.

Regards
Sharath
0
bsharath
• 15
• 14
• 2
• +1
1 Solution

Commented:

the column number you are searching your text is in 1

sub delete()
dim i as long
for i = 1 to 65000
if  cells(i,1) = "whatever you want"
rows(i).select
Selection.Delete Shift:=xlUp
i = i - 1
end if
next i
end sub
0

Author Commented:
I get this error.
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Syntax error
---------------------------
OK   Help
---------------------------

if  cells(i,1) = "whatever you want"
0

Commented:
use cell(i,1).value
0

Author Commented:
Can you post the whole edited macro
0

Commented:
sub delete()
dim i as long
for i = 1 to 65000
if  cells(i,1).value = "whatever you want"
rows(i).select
Selection.Delete Shift:=xlUp
i = i - 1
end if
next i
end sub
0

Author Commented:
This still gives a compile error.

if  cells(i,1).value = "whatever you want"
0

Commented:
ok got it try this
sub delete()
dim i as long
for i = 1 to 65000
if  Sheet1.cells(i,1).value = "whatever you want"
rows(i).select
Selection.Delete Shift:=xlUp
i = i - 1
end if
next i
end sub

0

Commented:
ok got it try this
sorry
sub delete()
dim i as long
for i = 1 to 65000
if  Sheet1.cells(i,1).value = "whatever you want"
Sheet1.rows(i).select
Selection.Delete Shift:=xlUp
i = i - 1
end if
next i
end sub
0

Author Commented:
Still get this error.

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Syntax error
---------------------------
OK   Help
---------------------------
0

Commented:
ok got it try this
sorry
sorry
sub delete()
dim i as long
for i = 1 to 65000
if  Sheet1.cells(i,1).value = "whatever you want" then
Sheet1.rows(i).select
Selection.Delete Shift:=xlUp
i = i - 1
end if
next i
end sub

0

Commented:
I have done some modification pls check
Sub del()
Dim i As Long
x = Application.InputBox("Enter Value")
For i = 1 To 65000
If Sheet1.Cells(i, 1).Value = Conversion.CStr(x) Then
Sheet1.Rows(i).Select
Selection.delete Shift:=xlUp
i = i - 1
End If
Next i
End Sub
End If
Next i
End Sub
0

Author Commented:
Hitesh

No error and i dont get a box to enter

Patel i get this.
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Only comments may appear after End Sub, End Function, or End Property
---------------------------
OK   Help
---------------------------
0

Commented:
ok check this
Sub del()
Dim i As Long
x = InputBox("Enter Value")
For i = 1 To 65000
If Sheet1.Cells(i, 1).Value = Str(x) Then
Sheet1.Rows(i).Select
Selection.delete Shift:=xlUp
i = i - 1
End If
Next i
End Sub
0

Commented:
Sub del()
Dim i As Long
x = Application.InputBox("Enter Value")
For i = 1 To 65000
If Sheet1.Cells(i, 1).Value = Conversion.CStr(x) Then
Sheet1.Rows(i).Select
Selection.delete Shift:=xlUp
i = i - 1
End If
Next i
End Sub
0

Author Commented:
patel.
I get no errors nor results.
I have this in the excel
Sharath   Reddy
ramesh  Sasi

When i enter sharath there is no changes.
0

Author Commented:
Ok got it.I think this works only for numbers....
Can it even search text and perform the same.
0

Commented:
ok try this
Sub del()
Dim i As Long
x = InputBox("Enter Value")
For i = 1 To 65000
If UCase(Sheet1.Cells(i, 1).Value) = UCase(Str(x)) Then
Sheet1.Rows(i).Select
Selection.delete Shift:=xlUp
i = i - 1
End If
Next i
End Sub

0

Author Commented:
Some issue in the macro.

The excel restarts and goes to recovery mode.
0

Commented:
ok try this
Sub del()
Dim i As Long
x = InputBox("Enter Value")
For i = 1 To 65000
If UCase(Sheet1.Cells(i, 1).Value) = UCase(Str(x)) Then
Sheet1.Rows(i).Select
Selection.delete Shift:=xlUp
End If
Next i
End Sub
0

Author Commented:
The same error. Excel restarting
0

Commented:
ok try this
Sub del()
Dim i As Long
x = InputBox("Enter Value")
For i = 1 To Sheet1.UsedRange.Rows.Count
If UCase(Sheet1.Cells(i, 1).Value) = UCase(Str(x)) Then
Sheet1.Rows(i).Select
Selection.delete Shift:=xlUp
End If
Next i
End Sub

0

Commented:
did this solve the problem?
0

Author Commented:
Same problem Excel restarting
0

Commented:
i dont understand why, just delete everything and paste the above lines again
0

Author Commented:
No same error.Any other way of doing this.
0

Commented:
ok try this
Sub deletesearch()
Dim i As Long
x = InputBox("Enter Value")
For i = 1 To Sheet1.UsedRange.Rows.Count
If UCase(Sheet1.Cells(i, 1).Value) = UCase(Str(x)) Then
Sheet1.Rows(i).Delete
End If
Next
End Sub
0

Author Commented:
Same i get an error and the excel restarts
0

Commented:
0

Author Commented:
0

Commented:
ok try this
Sub deletesearch()
Dim i As Long, x As String
x = InputBox("Enter Value")
For i = 1 To Sheet1.UsedRange.Rows.Count
If UCase(Sheet1.Cells(i, 1).Value) = UCase(x) Then
Sheet1.Rows(i).Delete
End If
Next
End Sub
0

Author Commented:
Ok this is fine.But when the data is in some other colum it does not delete
0

Commented:
ok try this
Here you go
Sub deletesearch()
Dim i As Long, x As String
x = InputBox("Enter Value")
For i = 1 To Sheet1.UsedRange.Rows.Count
For j=1 to Sheet1.UsedRange.Columns.Count
If UCase(Sheet1.Cells(i, j).Value) = UCase(x) Then
Sheet1.Rows(i).Delete
End If
Next
Next
End Sub

0

## Featured Post

• 15
• 14
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.