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:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



And, after the macro:

Excel 2007
ABCDEFGHIJK

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TABLE="width: 960"]
<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><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]
[TD="class: xl65, width: 64"]K[/TD]
[TD="class: xl65, width: 64"]L[/TD]
[TD="class: xl65, width: 64"]M[/TD]
[TD="class: xl65, width: 64"]N[/TD]
[TD="class: xl65, width: 64"]O[/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]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]Y[/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]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]20[/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]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]30[/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]
[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]
[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]
[TD="class: xl65"]K[/TD]
[TD="class: xl65"]L[/TD]
[TD="class: xl65"]M[/TD]
[TD="class: xl65"]N[/TD]
[TD="class: xl65"]O[/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]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10[/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]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]30[/TD]
[/TR]
</tbody>[/TABLE]
 
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

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]C[/TD]
[TD="bgcolor: #FFFFFF"]D[/TD]
[TD="bgcolor: #FFFFFF"]E[/TD]
[TD="bgcolor: #FFFFFF"]F[/TD]
[TD="bgcolor: #FFFFFF"]G[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]I[/TD]
[TD="bgcolor: #FFFFFF"]J[/TD]
[TD="bgcolor: #FFFFFF"]K[/TD]
[TD="bgcolor: #FFFFFF"]L[/TD]
[TD="bgcolor: #FFFFFF"]M[/TD]
[TD="bgcolor: #FFFFFF"]N[/TD]
[TD="bgcolor: #FFFFFF"]O[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Y[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



And, your results should look like this?

Excel 2007
ABCDEFGHIJKLMNO

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]A[/TD]
[TD="bgcolor: #FFFFFF"]B[/TD]
[TD="bgcolor: #FFFFFF"]C[/TD]
[TD="bgcolor: #FFFFFF"]D[/TD]
[TD="bgcolor: #FFFFFF"]E[/TD]
[TD="bgcolor: #FFFFFF"]F[/TD]
[TD="bgcolor: #FFFFFF"]G[/TD]
[TD="bgcolor: #FFFFFF"]H[/TD]
[TD="bgcolor: #FFFFFF"]I[/TD]
[TD="bgcolor: #FFFFFF"]J[/TD]
[TD="bgcolor: #FFFFFF"]K[/TD]
[TD="bgcolor: #FFFFFF"]L[/TD]
[TD="bgcolor: #FFFFFF"]M[/TD]
[TD="bgcolor: #FFFFFF"]N[/TD]
[TD="bgcolor: #FFFFFF"]O[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
291
Alex Blakenburg
A
A
  • Solved
Could not set the RowSource property. Invalid property value. Error 380
2
Replies
16
Views
210
RoryA
RoryA
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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