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
ABCDEFGHIJ
13102489446
Y4759988108
12786102183
RESULT
ABCDEFGHIJ
13102489446
12786102183

<tbody>
</tbody>
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
if a cell contains a "Y" then delete A2:J2 not entirerow.
Click to expand...

Lewzerrrr,

The following is based on your screenshots, not, on the above quote.

Here is a macro solution for you to consider.

Sample raw data:


Excel 2007
ABCDEFGHIJK
113102489446
2Y4759988108
312786102183
4
Sheet1


And, after the macro:


Excel 2007
ABCDEFGHIJK
113102489446
212786102183
3
4
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub Lewzerrrr()
' hiker95, 07/28/2017, ME1016458
Dim Addr As String
Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(@="""","""",IF((@<>""Y""),@,""#N/A""))", "@", Addr))
On Error GoTo NoDeletes
Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoDeletes:
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Lewzerrrr macro.
 
Last edited:
Upvote 0
hiker95 said:
Lewzerrrr,

The following is based on your screenshots, not, on the above quote.

Here is a macro solution for you to consider.

Sample raw data:

Excel 2007
ABCDEFGHIJK
113102489446
2Y4759988108
312786102183
4

<tbody>
</tbody>
Sheet1​



And, after the macro:

Excel 2007
ABCDEFGHIJK
113102489446
212786102183
3
4

<tbody>
</tbody>
Sheet1​



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub Lewzerrrr()
' hiker95, 07/28/2017, ME1016458
Dim Addr As String
Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(@="""","""",IF((@<>""Y""),@,""#N/A""))", "@", Addr))
On Error GoTo NoDeletes
Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoDeletes:
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Lewzerrrr macro.
Click to expand...

Hey thanks for this, I have data from K onwards so entirerow.delete can't be used, it would need to be ("A2:J2").delete shift:=xlup.

Also, can this be used automatically everytime something in the worksheet changes? e.g if i'm going to paste in the data then the code automatically runs.

I have to update this sheet quite a lot of and as there is lots of heavy formulas it may slow it down a lot, would adding code to turn off formula calculation at the start and reenable at the end work in this situation?

Thanks,
 
Last edited:
Upvote 0
I have data from K onwards so entirerow.delete can't be used, it would need to be ("A2:J2").delete shift:=xlup.
Click to expand...

Lewzerrrr,

1. Can I have a screenshot displaying raw data in A1:N4?

2. Can I have a screenshot of what the results should look like in A1:N4?
 
Upvote 0
hiker95 said:
Lewzerrrr,

1. Can I have a screenshot displaying raw data in A1:N4?

2. Can I have a screenshot of what the results should look like in A1:N4?
Click to expand...

ABCDEFGHIJKLMNO
131024894461010101010
Y31024894462020202020
127861021833030303030
RESULT
ABCDEFGHIJKLMNO
131024894461010101010
127861021832020202020
3030303030

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Last edited:
Upvote 0
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.
Click to expand...
The letter Y, does it only appear in col A or can be in col A to J?
When you paste in some data, do you mean paste it in col A to J or could be in another column?
When you paste in some data say in row 100, do you want the macro to look for Y starting from row 100 down, or starting from row 2?
 
Upvote 0
Lewzerrrr,

So, your actual raw data looks like this?


Excel 2007
ABCDEFGHIJKLMNO
1ABCDEFGHIJKLMNO
2131024894461010101010
3Y31024894462020202020
4127861021833030303030
5
Sheet1


And, your results should look like this?


Excel 2007
ABCDEFGHIJKLMNO
1ABCDEFGHIJKLMNO
2131024894461010101010
3127861021832020202020
43030303030
5
Sheet1



Are my above screenshots correct?
 
Upvote 0
Akuini said:
The letter Y, does it only appear in col A or can be in col A to J?
When you paste in some data, do you mean paste it in col A to J or could be in another column?
When you paste in some data say in row 100, do you want the macro to look for Y starting from row 100 down, or starting from row 2?
Click to expand...

The letter Y will only appear in column A.
I only paste in the data from A2:J2.. which can extend down to A2:J1000.
I would like it to start from A2 :)

hiker95 said:
Lewzerrrr,

So, your actual raw data looks like this?

Excel 2007
ABCDEFGHIJKLMNO
1ABCDEFGHIJKLMNO
2131024894461010101010
3Y31024894462020202020
4127861021833030303030
5

<tbody>
</tbody>
Sheet1​



And, your results should look like this?

Excel 2007
ABCDEFGHIJKLMNO
1ABCDEFGHIJKLMNO
2131024894461010101010
3127861021832020202020
43030303030
5

<tbody>
</tbody>
Sheet1​




Are my above screenshots correct?
Click to expand...

Correct :) It needs to delete for instance A2:J2 NOT entirerow.
 
Upvote 0
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]
 
Upvote 0
Lewzerrrr said:
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
ABCDEFGHIJ
13102489446
Y4759988108
12786102183
RESULT
ABCDEFGHIJ
13102489446
12786102183

<tbody>
</tbody>
Click to expand...
If you 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...
Code: 
[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  On Error GoTo NoYs
  Intersect(Columns("A:J"), Range("A2:A" & Rows.Count).SpecialCells(xlConstants, xlTextValues).EntireRow).Delete xlShiftUp
NoYs:
  Application.EnableEvents = True
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Fill any five cell only
Replies
3
Views
198
Peter_SSs
Peter_SSs
J
  • Solved
Help with calculation between work days based on condition
Replies
7
Views
280
Fluff
Fluff
J
  • Solved
IFS
Replies
4
Views
136
Peter_SSs
Peter_SSs
zookeepertx
  • Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
101
zookeepertx
zookeepertx
S
  • Solved
VBA Code Help
Replies
4
Views
307
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,575
Messages
6,125,620
Members
449,240
Latest member
lynnfromHGT

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