I have very messy data after sorted, how to delete? In this case, I wanted
to keep cell from A6 onwards. Thanks in advance.
A
1 -------- ---- ---+++ 8 %% ---
2 -------- *** dl lii --
3 setion line : 3546 098
4 Total : 98739 - 26254
5 ===== == ++ == ==
6 22413490013
7 27987542001
8 28213407096

Hi,

From the data you have posted you could do it with a simple sort and then
select and delete all the rows that begin with non-numeric characters.

Mike

"TPG" wrote:

I have very messy data after sorted, how to delete? In this case, I wanted
to keep cell from A6 onwards. Thanks in advance.
A
1 -------- ---- ---+++ 8 %% ---
2 -------- *** dl lii --
3 setion line : 3546 098
4 Total : 98739 - 26254
5 ===== == ++ == ==
6 22413490013
7 27987542001
8 28213407096

Thanks for your quite respond to my question. Infact my actual file is very
big (30k in term of rows), is there any other better way to solve it.
Thanks.

"Mike H" wrote:

Hi,

From the data you have posted you could do it with a simple sort and then
select and delete all the rows that begin with non-numeric characters.

Mike

"TPG" wrote:

I have very messy data after sorted, how to delete? In this case, I wanted
to keep cell from A6 onwards. Thanks in advance.
A
1 -------- ---- ---+++ 8 %% ---
2 -------- *** dl lii --
3 setion line : 3546 098
4 Total : 98739 - 26254
5 ===== == ++ == ==
6 22413490013
7 27987542001
8 28213407096

Another way
sub delrows()
for i=cells(rows.count,1).end(xlup).row to 2 step -1
if not isnumeric(cells(i,1)) then rows(i).delete
next i
end sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"TPG" wrote in message
...
I have very messy data after sorted, how to delete? In this case, I wanted
to keep cell from A6 onwards. Thanks in advance.
A
1 -------- ---- ---+++ 8 %% ---
2 -------- *** dl lii --
3 setion line : 3546 098
4 Total : 98739 - 26254
5 ===== == ++ == ==
6 22413490013
7 27987542001
8 28213407096

Hi,

Here are 3 suggestions:

1. Sort the data as suggested. 30000 lines doesn't make any difference.
Then all the items you want will be grouped together and you can select the
row numbers for all the rest and press Ctrl+- (ctrl minus)

2. Choose Data, Filter, AutoFilter and in the column where your numbers are
open the autofilter drop down and choose Custom. From the first drop down
pick greater than and in the second drop down enter a number maybe 100000000
or even 1. If the data you want to retain are numbers then everything else
will be hidden. Press Ctrl A. Then copy and move to a different sheet and
choose Paste.
This should copy only the visible cells.

3. Insert a blank column to the right of the column with number you want,
lets suppose that is column A, then in B1 enter the formula =1/ISNUMBER(A1)
This will return an error for all cells that don't contain numbers. Copy
this down the all the way to the bottom of your data. Select the column and
press Ctrl+G, Special, select Formulas, and uncheck all but Errors. After
you press OK, press Ctrl+- (control minus) and choose Entire row.

--
Thanks,
Shane Devenshire


"TPG" wrote:

I have very messy data after sorted, how to delete? In this case, I wanted
to keep cell from A6 onwards. Thanks in advance.
A
1 -------- ---- ---+++ 8 %% ---
2 -------- *** dl lii --
3 setion line : 3546 098
4 Total : 98739 - 26254
5 ===== == ++ == ==
6 22413490013
7 27987542001
8 28213407096