Delete rows containing a number

[hesham63]

hi
I need Automatic delete any rows which containing number starting with 333
Example
A1 : 333456789
A2 : 4567865555
A3 : 333 9876577
A4 : 3376879800

Become
A1 : 4567865555
A2 : 3376879800
Thanks

[martindwilson]

you could auto filter,custom,begins with 333 then delete the rows shown

[stanleydgromjr]

hesham63,

With your raw data in column A beginning in cell A1.


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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Delete333Rows()
' stanleydgromjr, 07/31/2011
' http://www.excelforum.com/excel-general/786463-delete-rows-containing-a-number.html
Dim LR As Long, a As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 1 Step -1
  If Left(Cells(a, 1), 3) = "333" Then Rows(a).Delete
Next a
Application.ScreenUpdating = True
End Sub

Then run the Delete333Rows macro.

[hesham63]

Thank you very much
Is it possible to automatically delete rows when writing

[stanleydgromjr]

hesham63,


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, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' stanleydgromjr, 08/01/2011
' http://www.excelforum.com/excel-general/786463-delete-rows-containing-a-number.html
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
With Application
  .ScreenUpdating = False
  .EnableEvents = False
  If Left(Target, 3) = "333" Then Rows(Target.Row).Delete
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Then make entries in column A. And, after you press the ENTER key, or select one of the arrow keys to navigate, if the first three characters of what you typed in is 333, then the row will get deleted.

[hesham63]

Thank you very much