This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / Question for EXCEL function: how to delete multiple rows where the cell/s contains the data by the Find function?1, using "Find ALL" function find all cells contain some data. e.g. found 1000 rows with these cells
2. how to delete all 1000 rows in one shot?
Many thanks.
-chyj-forest(forest);
2012-6-5
{163}
(#7570394@0)
-
Atop of my head, can think of 2 ways -- 1) try UltraEdit and 2) Make up a small VBA macro.
-c__wang(安全ID);
2012-6-5
(#7570527@0)
-
If it's text, I would try finding all and replacing with nothing. Otherwise, you'll need to design a macro. Just my 2 cents.
-yuxiulong(Jen);
2013-8-8
(#8323257@0)
-
I guess you're talking about the cells of the same column or few columns, say, less than 5 or 6. If that's the case, just filter those rows meeting your criteria.If too many columns but not too many rows you may want to use the Condition Formatting to highlight those cells and manually delete those rows. If it's big worksheet including many columns you want to filter on and too many records to do it manually, your best rescue is: SQL - after importing to a DBMS.
-renjl0810(Virtual Void);
2012-6-5
{305}
(#7570574@0)
-
if the cells you found are in the same column, 1. do find and replace them to blank, 2. sort this column, then delete. if the cells are in different column, VBA or SQL
-alice06(4颗);
2012-6-6
(#7573398@0)
-
No need SQL/VBA. Use an extra column and combine on the cell in the same row into one cell. And find in that cell. In Excel, it is VERY important that you stay away from VBA until it is absolutely necessary.
-jeffrey815(Smartiecat);
2012-6-6
(#7573527@0)
-
Really appreciated all of you for the replies.
some additional info about my question1. the cells found are in different columns, say column d, f, h
2,the cells found are in different rows as well, say totally 500 rows
3. the cells contains other text besides the word used in the Find function.
I just do not want to delete row by row.
Thanks again.
-chyj-forest(forest);
2012-6-6
{275}
(#7573677@0)
-
As I said, use an additional column and combined all the column into this column (ie. A1&A2&A3, or A1&"_"&A2&"_"&A3, etc), then find on this additional column only. If find return something, mark it, then either filter on it or sort it, then delete.
-jeffrey815(Smartiecat);
2012-6-7
(#7573706@0)
-
The question is how to mark it. If it still needs marking manually, what’s difference by deleting row by row manually?
-deep_blue(BLUE);
2012-6-7
(#7573797@0)
-
=isnumber(FIND("find_test",D1))
-jeffrey815(Smartiecat);
2012-6-7
(#7574171@0)
-
My question is how to apply the rule to each row.
-deep_blue(BLUE);
2012-6-7
(#7574252@0)
-
copy and paste the formula
-jeffrey815(Smartiecat);
2012-6-7
(#7574260@0)
-
Visual Basic/Excel Questions.I am in the Finance field. Now and then, my predesigned excel book gives me errors. I have been googling to resolve them. It worked but quite time consuming. I was wondering if I should take Visual Basic. If yes, which school/teachers would you prefer. Any hints would be greatly appreciated.
-yuxiulong(Jen);
2013-8-8
{292}
(#8323226@0)