Mr. "Iamthe Best" needs your help !!! How to delete a set of rows

[Iamthe best]

Hello,

I have a set of rows from a total 20000 rows, which have some strings.
The entire worksheet consists of five colums of numbers. I am trying to delete these rows by using some formula that could be dragged down a column. Lets say I have this :

123 1444 155 5151
121 1212 141 1515
string
123 1444 155 5151
121 1212 141 1515
string
123 1444 155 5151
121 1212 141 1515

So, I just want to delete the rows with strings by dragging a formula on the last column that does exactly that. Could anyone show me how to do this by any method whasoever. Thanks.

[mudraker]

Please read forum rules - see links below

Your thread title is invalid.

Formulas can not delete rows but they can assist you to delete rows

Try this
In a blank column enter this formula
=IF(ISNUMBER(A1),"","String")
Change A1 as required

If Row 1 does not have column headers insert a new row 1, Add column headers.

once columns have headers
Select row 1
from the Data Menu >
select Filter >
Autofilter >
Select Dropdown arrow for column that has formula > Select String
Select displayed rows & Delete

[sweep]

Hi,

Copy this code. In excel press ALT&F11 then F7. Paste the code. Close the window.

Please Login or Register  to view this content.

In excel go to Tools > Macro > Run Macro > Delrows > OK

[starguy]

Hello,

I have a set of rows from a total 20000 rows, which have some strings.
The entire worksheet consists of five colums of numbers. I am trying to delete these rows by using some formula that could be dragged down a column. Lets say I have this :

123 1444 155 5151
121 1212 141 1515
string
123 1444 155 5151
121 1212 141 1515
string
123 1444 155 5151
121 1212 141 1515

So, I just want to delete the rows with strings by dragging a formula on the last column that does exactly that. Could anyone show me how to do this by any method whasoever. Thanks.

You can do it without the need of formula. (better is to copy your data to other sheet before doing the following)

I suppose that you mean any text entered where you have shown word "string"
Select the column or columns (if you have strings in more columns).
press Ctrl+H
in Replace what enter 'a' (without quotes) and press Replace All.
then enter 'b' and press Replace All
and so on for all 26 characters of English. (don't worry it will take less than a minute.) all strings will be removed and blank cells appear in place of strings.
Now to remove blanks cells, select col A
press F5 then Special button and select Blanks.
then right click on any selected cell, right click > Delete > Entire row then press Ok.

hope this would help.