VBA: Delete range if cell contains "Y"

L

Lewzerrrr

Active Member
Joined
Jan 18, 2017
Messages
256
Hi,

I need to create a code that once I paste in some data, the code activates and looks down range("A2:A10000") (Or last row, the data will never go past 10000) and if a cell contains a "Y" then delete A2:J2 not entirerow.

Example
[TABLE="width: 640"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]Y[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]8[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]RESULT[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A[/TD]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]D[/TD]
[TD="class: xl65"]E[/TD]
[TD="class: xl65"]F[/TD]
[TD="class: xl65"]G[/TD]
[TD="class: xl65"]H[/TD]
[TD="class: xl65"]I[/TD]
[TD="class: xl65"]J[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Sort by date Sort by votes

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Akuini said:
Ok, try this:
Code: 
[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Worksheet_Change[B]([/B][COLOR=blue]ByVal[/COLOR] Target [COLOR=blue]As[/COLOR] Range[B])[/B]
 
    [COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] Intersect[B]([/B]Target[B],[/B] Range[B]([/B][COLOR=brown]"A2:J10000"[/COLOR][B]))[/B] [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
    [COLOR=blue]Dim[/COLOR] r [COLOR=blue]As[/COLOR] Range[B],[/B] c [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] d [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
        [COLOR=blue]On[/COLOR] [COLOR=blue]Error[/COLOR] [COLOR=blue]GoTo[/COLOR] skip[B]:[/B]
            Application.EnableEvents [B]=[/B] [COLOR=blue]False[/COLOR]
                Application.ScreenUpdating [B]=[/B] [COLOR=blue]False[/COLOR]
                    d [B]=[/B] Range[B]([/B][COLOR=brown]"A:A"[/COLOR][B]).[/B]Find[B]([/B][COLOR=brown]"Y"[/COLOR][B],[/B] SearchDirection[B]:=[/B]xlNext[B]).[/B]Row
   
        [COLOR=blue]With[/COLOR] Range[B]([/B][COLOR=brown]"A2"[/COLOR][B],[/B] Cells[B]([/B]Rows.Count[B],[/B] [COLOR=brown]"A"[/COLOR][B]).[/B][COLOR=blue]End[/COLOR][B]([/B]xlUp[B]))[/B]
       
            [COLOR=blue]Set[/COLOR] r [B]=[/B] [B].[/B]Find[B]([/B]What[B]:=[/B][COLOR=brown]"Y"[/COLOR][B],[/B] LookIn[B]:=[/B]xlValues[B],[/B] LookAt[B]:=[/B]xlWhole[B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] _
                SearchDirection[B]:=[/B]xlPrevious[B],[/B] MatchCase[B]:=[/B][COLOR=blue]True[/COLOR][B],[/B] SearchFormat[B]:=[/B][COLOR=blue]False[/COLOR][B])[/B]
           
                [COLOR=blue]Do[/COLOR]
                   c [B]=[/B] r.Row
                      [COLOR=blue]Set[/COLOR] r [B]=[/B] [B].[/B]FindPrevious[B]([/B]r[B])[/B]
                   Cells[B]([/B]c[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]).[/B]Resize[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] [B][COLOR=crimson]10[/COLOR][/B][B]).[/B]Rows.Delete Shift[B]:=[/B]xlUp
                [COLOR=blue]Loop[/COLOR] [COLOR=blue]While[/COLOR] c [B]>[/B] d
           
        [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
   
skip[B]:[/B]
        Application.EnableEvents [B]=[/B] [COLOR=blue]True[/COLOR]
        Application.ScreenUpdating [B]=[/B] [COLOR=blue]True[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]
Click to expand...

Absolutely perfect, thank you so much :)
 
Upvote 0
Ingolf said:
@Rick,

Your code delete any range A:J if column A contain ANY letter, not only letter "Y"
Click to expand...
I know that which is why I wrote this above the code I posted...

"If your data in Column A is as shown (either numbers
or the letter Y in Rows 2 on down), then you can use
this Worksheet_Change event code..."

I guess you didn't read that, did you?
 
Last edited:
Upvote 0
Lewzerrrr said:
Absolutely perfect, thank you so much :)
Click to expand...
If the values in Column A will only be numbers or the letter "Y", then you can use the Worksheet Change event code I posted in Message #10 above. However, if Column A can contain other non-digit characters besides the "Y", then you can use this alternate Worksheet Change event code instead...
Code: 
[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Columns("A").Replace "Y", "#N/A", xlWhole, , False, , False, False
  Application.EnableEvents = False
  On Error GoTo NoYs
  Intersect(Columns("A:J"), Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow).Delete xlShiftUp
  Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
NoYs:
  Application.EnableEvents = True
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Rick Rothstein said:
If the values in Column A will only be numbers or the letter "Y", then you can use the Worksheet Change event code I posted in Message #10 above. However, if Column A can contain other non-digit characters besides the "Y", then you can use this alternate Worksheet Change event code instead...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Columns("A").Replace "Y", "#N/A", xlWhole, , False, , False, False
  Application.EnableEvents = False
  On Error GoTo NoYs
  Intersect(Columns("A:J"), Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow).Delete xlShiftUp
  Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
NoYs:
  Application.EnableEvents = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Both of them work exactly how I need them to, I love the simplicity of yours :)

My data source doesn't actually have numbers, it will only ever contain a "" or a "Y" so all 3 work :)

It's also very good because I can use it to delete the range if I just type in a Y

Thanks,
 
Upvote 0
Lewzerrrr,

Here is a macro solution for you to consider based on your screenshots from your reply 5.

Sample raw data:


Excel 2007
ABCDEFGHIJKLMNOP
1131024894461010101010
2Y31024894462020202020
3127861021833030303030
4
Sheet1


And, after the new macro:


Excel 2007
ABCDEFGHIJKLMNOP
1131024894461010101010
2127861021832020202020
33030303030
4
Sheet1





Code: 
Sub Lewzerrrr_V2()
' hiker95, 07/29/2017, ME1016458
Application.ScreenUpdating = False
Dim lr As Long, lc As Long, r As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
lc = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
For r = lr To 1 Step -1
  If Range("A" & r) = "Y" Then
    Range("A" & r).Resize(, 10).ClearContents
    Range("A" & r + 1).Resize(lr, 10).Cut Range("A" & r)
  End If
Next r
Application.ScreenUpdating = True
End Sub

With the same instructions as my reply #2.

Then run the Lewzerrrr_V2 macro.
 
Last edited:
Upvote 0
Rick Rothstein said:
If the values in Column A will only be numbers or the letter "Y", then you can use the Worksheet Change event code I posted in Message #10 above. However, if Column A can contain other non-digit characters besides the "Y", then you can use this alternate Worksheet Change event code instead...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Columns("A").Replace "Y", "#N/A", xlWhole, , False, , False, False
  Application.EnableEvents = False
  On Error GoTo NoYs
  Intersect(Columns("A:J"), Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow).Delete xlShiftUp
  Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
NoYs:
  Application.EnableEvents = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Beautiful code, Rick. One for my useful code list. Thanks for sharing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
300
Alex Blakenburg
A
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
267
RoryA
RoryA
hardeep.kanwar
  • Question Question
Paste data based on cell color
Replies
2
Views
62
Peter_SSs
Peter_SSs
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
74
daveyc18
D
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
59
Skyybot
Skyybot

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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