Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting several rows with given criteria
I have 1 thousand lines/rows in a sheet.
I want to delete rows that contains "0100" text or maybe number. Thanks. Rechie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting several rows with given criteria
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. "Rechie" wrote: I have 1 thousand lines/rows in a sheet. I want to delete rows that contains "0100" text or maybe number. Thanks. Rechie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting several rows with given criteria
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 On Oct 20, 1:52*pm, Rechie wrote: I have 1 thousand lines/rows in a sheet. I want to delete rows that contains "0100" text or maybe number. Thanks. Rechie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting several rows with given criteria
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. "Sagar" wrote: 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. "Rechie" wrote: I have 1 thousand lines/rows in a sheet. I want to delete rows that contains "0100" text or maybe number. Thanks. Rechie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting several rows with given criteria
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: 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. "Sagar" wrote: 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. "Rechie" wrote: I have 1 thousand lines/rows in a sheet. I want to delete rows that contains "0100" text or maybe number. Thanks. Rechie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting several rows with given criteria
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 --------------- Jacob Skaria "Rechie" wrote: I have 1 thousand lines/rows in a sheet. I want to delete rows that contains "0100" text or maybe number. Thanks. Rechie |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting several rows with given criteria
It's working, Thanks Stefi
"Stefi" wrote: 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: 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. "Sagar" wrote: 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. "Rechie" wrote: I have 1 thousand lines/rows in a sheet. I want to delete rows that contains "0100" text or maybe number. Thanks. Rechie |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting several rows with given criteria
It's working, Thanks!
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Rechie" wrote: I have 1 thousand lines/rows in a sheet. I want to delete rows that contains "0100" text or maybe number. Thanks. Rechie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows by specified criteria for duplicates | Excel Discussion (Misc queries) | |||
Deleting Rows w/ Specific Criteria | Excel Discussion (Misc queries) | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Deleting/IDing Rows that Don't Meet Criteria | Excel Worksheet Functions | |||
Deleting/IDing Rows that Don't Meet Criteria | Excel Worksheet Functions |