For each cell value in column A

fari1

fari1

Active Member
Joined
May 29, 2011
Messages
362
hi, i want to do a loop, which will do the following
for every cell value in column A of sheet1, it'll find those value in sheet2 column A, if finds, then it'll delete that row,
i've a code that does this, but it is based upon the fixed value, while istead of a fixed value, i want to define a column for this

Code: 
Sub deletefor()
startrow = 2
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
With Application
  CurrentScreenUpdating = .ScreenUpdating
  CurrentCalculate = .Calculation
  CurrentEnableEvents = .EnableEvents
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
  .EnableEvents = False
End With
For X = lastrow To startrow Step -1
  If Cells(X, "A").Value Like "*for*" Then Cells(X, "A").EntireRow.Delete
Next
With Application
  .ScreenUpdating = CurrentScreenUpdating
  .Calculation = CurrentCalculate
  .EnableEvents = CurrentEnableEvents
End With
End Sub


please help me with this:(
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
See if this works..

I excluded the "With Application" code to keep it simple.... you may add it back.

Code: 
Sub deletefor()
startrow = 2

sheets("sheet2").select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

sheet1("sheet1").select
sheet1rows = Cells(Rows.Count, "A").End(xlUp).Row

For Y = 2 to sheet1rows
   sheet1("sheet1").select
   searchvalue = cells(Y, "A").value
   sheets("sheet2").select
   For X = lastrow To startrow Step -1
      If Cells(X, "A").Value = searchvalue Then Cells(X, "A").EntireRow.Delete
   Next

Next

End Sub
 
Upvote 0
hi saagar, its working perfect, just one little problem, it is looking for one fixed value in the whole column, my scenerio is that in sheet2, in every cell, i've long text strings, and the word to find can be anywhere in those long strings.
e.g i want to delete the rows that contain apple
then this code is deleting the row, when there's just apple in the row, while i want it to find any where in the cell the word apple and delete that row
hope i made my point clear.
 
Upvote 0
e.g its deletes all the rows, where there's just apple in a single cell, it wont consider those rows where there's apple in the long text strings. say in row A5 i've text string, i like apple, then it wont delete that row, while i want it to delete that as well.
 
Upvote 0
Try this:

Code: 
Sub deletefor()
startrow = 2

sheets("sheet2").select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

sheets("sheet1").select
sheet1rows = Cells(Rows.Count, "A").End(xlUp).Row

For Y = 2 to sheet1rows
   sheet1("sheet1").select
   searchvalue = "*" & cells(Y, "A").value & "*"
   sheets("sheet2").select
   For X = lastrow To startrow Step -1
      If Cells(X, "A").Value like searchvalue Then Cells(X, "A").EntireRow.Delete
   Next

Next

End Sub
 
Upvote 0
hi saagar the code is perfect, but want a few things with it, firstly its case sensetive, for every item in column A of sheet1, if that value is like "Apple", then it would delete only those rows, which contain Apple will not delete those which are like apple and APPLE.i want it to delete every row, irrespective of going into case sensetive stuff.
the other thing is rather than a fixed sheet i-e sheet2, i want it to be general, meaning when it is run in sheet4, it must work there, and if run in sheet5 it must run there, i dont want to change sheet in the code each time i run it.

thanks alot for your wonderful code, i really needed it desparately, would much appreciate, if u help on this part as well.
thanks again
 
Upvote 0
Here's another method

Code: 
Sub delfromRange()
    Dim valRange As Range, v As Variant
    Application.ScreenUpdating = False
    If ActiveSheet.Name <> "sheet1" Then
        With Sheets("sheet1")
            Set valRange = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
        End With
        With ActiveSheet
            For Each v In valRange.Cells
                Set f = .Columns(1).Find(what:=v, lookat:=xlPart, MatchCase:=False)
                Do While Not f Is Nothing
                    r = f.Row
                    f.EntireRow.Delete
                    Set f = .Columns(2).FindNext(.Cells(r, 1))
                Loop
            Next v
        End With
    End If
End Sub
This should run whatever sheet you're in, so long as it's not sheet1.

Also, it's not case sensitive, and it ought to be a little quicker, since it doesn't require looping through every row.
 
Last edited:
Upvote 0
This should take care of case sensitive stuff.

Code: 
Sub deletefor()
startrow = 2

sheets("sheet2").select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

sheets("sheet1").select
sheet1rows = Cells(Rows.Count, "A").End(xlUp).Row

For Y = 2 to sheet1rows
   sheet1("sheet1").select
   searchvalue = "*" & lcase(cells(Y, "A").value) & "*"
   sheets("sheet2").select
   For X = lastrow To startrow Step -1
      If lcase(Cells(X, "A").Value) like searchvalue Then Cells(X, "A").EntireRow.Delete
   Next

Next

End Sub
The current code gets values from sheet1 and deletes rows in sheet2.
Do you want sheet1 to be variable in the code or sheet2?
 
Upvote 0
oh i didn't see i got replies, my mailbox has got realy tired i guess, i was waiting and i couldn't see the replies.
well thanks guys for the replies.

@waever, the code is giving error
unable to get the find next property of range class
at this line of code

Set f = .Columns(2).FindNext(.Cells(r, 1))


for saagar its doing some weired stuff, not working properly, deleting every row, i want sheet1 to have that column which contains text values to be deleted from other sheets
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Delete Row if
Replies
6
Views
175
Eric Penfold
E
N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
208
MARK858
MARK858
J
  • Solved
Speeding up VBA
Replies
9
Views
245
jvoss
J
M
  • Question Question
code to include a column or more based on checkbox
Replies
6
Views
146
mecerrato
M
S
  • Solved
Macro to filter each column for cells that appear empty, clear the contents and move to the next column for entire table
Replies
3
Views
554
sspatriots
S

Forum statistics

Threads
1,224,551
Messages
6,179,480
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top