Deleting rows via an if statement

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
389
Hi all,

Firstly, Merry Christmas to everyone who has used mrexcel over the past year.

i have an userform which inserts into cell a2, a text field, say "membership income".(please note that cell a2 is variable) and cell a2 should be matched upto cells in column d.

how would u develop a macro that will check all the cells in column d equals a2 or not.

so if cell a2=dx then do not delete row x where x is the row number.

examples being....

if a2=d1 then do not delete row
if a2 does not equal d2 then delete row 2
if a2 does not equal d3 then delete row 3 etc
upto row 65536.

Cheers

Jay
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi Jay, and Merry Christmas.
This will do what I think you're asking for. I have changed the range to search for matches for to include D3:D65536 instead of starting at D1.
The reason for this is if there ever is a match between D2 and A2, it deletes row 2, removing the value that is being looked for in the remaining rows, and creating and endless loop.
Hope this either works well enough for you or gives you some ideas of how to do what you want.
Code:
Sub DeleteA2Matches()
Dim rng As Range
Set rng = Range("D3:D65536")
For Each cell In rng
If cell = [A2].Value Then
cell.EntireRow.Delete
End If
Next cell
End Sub
(Ho,Ho,Ho.)
Dan
 
Upvote 0
This'll check to make sure something's in A2 before it torches your entire sheet; Starts at the bottom, works its way up, will delete rows 2 and 1 if needed. Always test on a backup...


Code:
Sub ZapRows()

Dim TargetValue As String, i as Long, LastRow as Long
Application.ScreenUpdating = False
On Error GoTo Xit

With ActiveSheet
    LastRow =  [D65536].End(3).Row
    TargetValue = [A2]
    If (Len(TargetValue)) Then
        For i = LastRow To 1 Step -1
            If .Cells(i, 4) <> TargetValue Then
                .Rows(i).EntireRow.Delete
            End If
        Next i
    End If
End With

Xit:
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Hi Jay,
Got to thinking about it and changed the code in my first post slightly.
This will do what you've asked, including the range D1:D65536 and has no problem if A2 and D2 match. (And checks to see that A2 is not blank as Jon has done...hell of a good idea!)
Code:
Sub DeleteRowIfA2MatchesCellInD()
Dim rng As Range, a As Variant
a = [A2].Value
If a <> vbNullString Then
Set rng = Range("D1:D65536")
For Each cell In rng
If cell = a Then
cell.EntireRow.Delete
End If
Next cell
End If
End Sub

I haven't tried Jon's code, so his may be somewhat faster as he's pretty good, but this is working well for me.

Merry Christmas (to you too Jon!)
Dan
 
Upvote 0
Hello Half Ace:
I haven't tried your code but looking at it makes me think you will have a problem if two of the rows needing to be deleted are right after each other. For example let's say you need to delete Row5 and Row6 ... Now if you delete row5 then Row6 is moved up to become Row5 ... since you already evaluated row 5 it will not be evaluated again. This would mean the "new" row 5 (ie. the old row 6f) will not be evaluated.

Generally , when you are deleting entire rows , the evaluation needs to be started at the Highest numbered row :wink:

Again I must say that I'm on the road and haven't tested your code so please accept my aplologies if I've misunderstood how it will function. (y)
 
Upvote 0
Here's another procedure that avoids using a loop. If you have a lot of data to process, this procedure will be much quicker :-

Code:
Sub ZapRows()
Dim rng$
Dim lastCell As Range
rng = [A2].Value
If Len(rng) = 0 Then Exit Sub
If Len([D65536]) <> 0 Then
    Set lastCell = [D65536]
Else
    Set lastCell = [D65536].End(xlUp)
End If
Application.ScreenUpdating = False
[D:D].Insert
With Range([D1], lastCell(1, 0))
    .FormulaR1C1 = "=IF(RC[1]=R2C1,1,"""")"
    .Value = .Value
    .EntireRow.Sort Key1:=[D1], Order1:=xlAscending, Header:=xlNo
    On Error Resume Next
    .SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
End With
[D:D].Delete
[A2] = rng
Application.ScreenUpdating = True
End Sub

This procedure checks column D starting from D1 and retains the value in A2 in the event that row 2 is deleted.
If you do not want to retain this value, delete the line that reads : [A2] = rng
 
Upvote 0
Looks like Ponsy Nob. has a winner.
Had to make a few very minor changes but this one is good.
Originally, it deleted all rows that did not match A2, and if A2 & D2 matched, it left A2's value in A1 and A2.
The following are the changes I needed to make to get it to work for me.

Dim rng$
Dim lastCell As Range
rng = [A2].Value
If Len(rng) = 0 Then Exit Sub
If Len([D65536]) <> 0 Then
Set lastCell = [D65536]
Else
Set lastCell = [D65536].End(xlUp)
End If
Application.ScreenUpdating = False
[D:D].Insert
With Range([D1], lastCell(1, 0))
.FormulaR1C1 = "=IF(RC[1]<>R2C1,1,"""")"
.Value = .Value
.EntireRow.Sort Key1:=[D1], Order1:=xlAscending, Header:=xlNo
On Error Resume Next
.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End With
[D:D].Delete
[A2] = rng
[A1] = ""
Application.ScreenUpdating = True
End Sub

Nice one Ponsy.

Now how would I be able to make the code I wrote work? As Nimrod (correctly) pointed out if there are two consecutive rows with a match to A2, the second of those two does not get deleted. I’m thinking I would need to use something along the lines of:
LastRow = [D65536].End(3).Row and For i = LastRow To 1 Step –1
like Jon did, but I can’t quite get it right. Am I even on the right track here?

Thanks,
Dan
 
Upvote 0
Ponsy/HA's structures will be faster than my looping; not that comfortable yet working w/ objects so I wrote what I know.

HalfAce -- haven't tried it -- slothfull moi -- but I think your code should work as is; since you are working with a range object, I don't think you have to worry about two consecutive delete rows as I did in my loop... if I get some time, I'll test it out.
 
Upvote 0
Thanks for all your help......

say if you wish to extract the information rather than delete the information you dont want to see......

so, if cell a2=d2 then cut and paste row2,
if cell a2=d3 then cut and paste row3,
if cell a2 does not equal d4, then check if a2=d5 etc....

say if the cut and paste goes to worksheet called ExtractData

Many thanks

Jay
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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