VBA: Delete range if cell contains "Y"

[Lewzerrrr]

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

A	B	C	D	E	F	G	H	I	J
1	3	10	2	4	8	9	4	4	6
Y	4	7	5	9	9	8	8	10	8
1	2	7	8	6	10	2	1	8	3
RESULT
A	B	C	D	E	F	G	H	I	J
1	3	10	2	4	8	9	4	4	6
1	2	7	8	6	10	2	1	8	3

<tbody>
</tbody>

 

[Ingolf]

@Rick,

Your code delete any range A:J if column A contain ANY letter, not only letter "Y"

 

[Lewzerrrr]

Ok, try this:

[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]

Absolutely perfect, thank you so much

 

[Rick Rothstein]

@Rick,

Your code delete any range A:J if column A contain ANY letter, not only letter "Y"

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?

 

[Rick Rothstein]

Absolutely perfect, thank you so much

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...

[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]

 

[Lewzerrrr]

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...

[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]

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,

 

[hiker95]

Lewzerrrr,

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

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	3	10	2	4	8	9	4	4	6	10	10	10	10	10
2	Y	3	10	2	4	8	9	4	4	6	20	20	20	20	20
3	1	2	7	8	6	10	2	1	8	3	30	30	30	30	30
4
Sheet1

And, after the new macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1	3	10	2	4	8	9	4	4	6	10	10	10	10	10
2	1	2	7	8	6	10	2	1	8	3	20	20	20	20	20
3											30	30	30	30	30
4
Sheet1

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.

 

[Rick Rothstein]

Lewzerrrr,

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

See the OP's second sentence in his reply to me in Message #15.

 

[Akuini]

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...

[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]

Beautiful code, Rick. One for my useful code list. Thanks for sharing.