Deleting several rows with given criteria

R

Rechie

I have 1 thousand lines/rows in a sheet.
I want to delete rows that contains "0100" text or maybe number.

Thanks.
Rechie
 
S

Sagar

you may try applying auto filter. and then select the text and delete. (if
this is one time requirement). otherwise, do the same thing with record macro
options. and modify the code to automize this.
 
M

muddan madhu

Sub remove()

Rng = Cells(Rows.Count, "A").End(xlUp).Row
For i = Rng To 1 Step -1
With Application.WorksheetFunction
If .CountIf(Rows(i), "0100") = 1 Then
Rows(i).EntireRow.Delete
End If
End With
Next i
End Sub
 
R

Rechie

It will be very strenuous to delete thru Auto filter of data contains "0100"
as it is sitiauted at the middle of the text (with prefix and suffix). So it
can not be filtered.
See Sample data in the sheet:
A010040
A010030
A020060
A020020
and so forth.
 
S

Stefi

It CAN be filtered: Select Custom from Autofilter drop down list, select
"contains" from Show rows where list and enter 0100 in the value box next to
it!

Regards,
Stefi

„Rechie†ezt írta:
 
J

Jacob Skaria

You can try out the below macro which will look at ColumnA and delete all
entries which satisfy the criteria. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Range("A" & lngRow) Like "*0100*" Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes
 
R

Rechie

It's working, Thanks Stefi


Stefi said:
It CAN be filtered: Select Custom from Autofilter drop down list, select
"contains" from Show rows where list and enter 0100 in the value box next to
it!

Regards,
Stefi

„Rechie†ezt írta:
Click to expand...
 
R

Rechie

It's working, Thanks!



Jacob Skaria said:
You can try out the below macro which will look at ColumnA and delete all
entries which satisfy the criteria. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Range("A" & lngRow) Like "*0100*" Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes
Click to expand...
 
Top