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

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

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
Asked:
bsharath
  • 15
  • 14
  • 2
  • +1
1 Solution
 
mandeliaCommented:


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
 
bsharathAuthor Commented:
I get this error.
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

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

if  cells(i,1) = "whatever you want"
0
 
hiteshgoldeneyeCommented:
use cell(i,1).value
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bsharathAuthor Commented:
Can you post the whole edited macro
0
 
hiteshgoldeneyeCommented:
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
 
bsharathAuthor Commented:
This still gives a compile error.

if  cells(i,1).value = "whatever you want"
0
 
hiteshgoldeneyeCommented:
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
 
hiteshgoldeneyeCommented:
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
 
bsharathAuthor Commented:
Still get this error.

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

Syntax error
---------------------------
OK   Help  
---------------------------
0
 
hiteshgoldeneyeCommented:
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
 
namanpatelCommented:
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
 
bsharathAuthor 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
 
hiteshgoldeneyeCommented:
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
 
namanpatelCommented:
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
 
bsharathAuthor 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
 
bsharathAuthor Commented:
Ok got it.I think this works only for numbers....
Can it even search text and perform the same.
0
 
hiteshgoldeneyeCommented:
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
 
bsharathAuthor Commented:
Some issue in the macro.

The excel restarts and goes to recovery mode.
0
 
hiteshgoldeneyeCommented:
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
 
bsharathAuthor Commented:
The same error. Excel restarting
0
 
hiteshgoldeneyeCommented:
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
 
hiteshgoldeneyeCommented:
did this solve the problem?
0
 
bsharathAuthor Commented:
Same problem Excel restarting
0
 
hiteshgoldeneyeCommented:
i dont understand why, just delete everything and paste the above lines again
0
 
bsharathAuthor Commented:
No same error.Any other way of doing this.
0
 
hiteshgoldeneyeCommented:
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
 
bsharathAuthor Commented:
Same i get an error and the excel restarts
0
 
hiteshgoldeneyeCommented:
can you upload the file?
0
 
bsharathAuthor Commented:
0
 
hiteshgoldeneyeCommented:
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
 
bsharathAuthor Commented:
Ok this is fine.But when the data is in some other colum it does not delete
0
 
hiteshgoldeneyeCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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