Hello,
Not fully tested :
Sub DeleteRows()
Dim i As Long, Res As Long
For i = Cells(Cells.Rows.Count, 1).End(xlUp).Row To 2 Step -1
If IsNumeric(Split(Cells(i, 1), " ")(0)) And Mid(Cells(i, 1).Value, 2,
1) = " " Then
Res = Split(Cells(i, 1), " ")(0) * 1
Exit For
End If
Next i
For i = Cells(Cells.Rows.Count, 1).End(xlUp).Row To 2 Step -1
If IsNumeric(Split(Cells(i, 1), " ")(0)) And Mid(Cells(i, 1).Value, 2,
1) = " " Then
If Split(Cells(i, 1), " ")(0) * 1 <= Res Then
Res = Split(Cells(i, 1), " ")(0) * 1
Else
Cells(i, 1).EntireRow.Delete
End If
Else
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub
Regards.
Daniel
-----Message d'origine-----
De : excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] De
la part de CAN
Envoyé : vendredi 5 novembre 2010 19:32
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Fwd: Delete rows from a Sheet based on value found
on previous cell
I posted this discussion a few days ago and I haven't hear any answers on
it. Does anyone can help ?
Thank you
---------- Forwarded message ----------
From: CAN <cdelano...@gmail.com>
Date: Nov 3, 10:58 am
Subject: Delete rows from a Sheet based on value found on previous cell
To: MS EXCEL AND VBA MACROS
I have a large excelsheetthat contains all the values on Cell A (this was
copied and pasted from PDF).
On each cell under column A there are 3 to 4rowsthat below belonfg to the
same customer. In other words a customer can values in A1, A2,
A3 and A4. Then the next customer starts with values on A5, A6, A7, A8,
etc, etc.
The first row of each customer will show a sequence of numbers that will
grow per customer. This is the first information found on each customer.
For instance on A1 the first information is 1, on A5 the first information
is 2, on A9 the first information is 3. These values will grow in numeric
fashion from 1 to 9999.
What I need is a macro that will look in each row and find this value as it
grows (1,2,3,4,5, etc) Whenever the macros finds this value it will
indicate that this is a row to keep, then looks at the following row and if
it doesn't find the next number on the sequence it willdeletethe row., then
loop again to look at the next row.
In other words. Based on the same below. The initial data will look like
this:
1 1101T DAWN BARRETT
MARJORIE BARRETT
10982 SW 247TH TER
HOMESTEAD FL 33032-4694
2 1102T MILENA
RODRIGUEZ
10978 SW 247TH TER
HOMESTEAD FL 33032-4694
3 1103T JOAN MAIR
ROSA DE MATA
10974 SW 247TH TER
HOMESTEAD FL 33032-4694
4 1104T DANIEL
WENGIER
10970 SW 247TH TER
HOMESTEAD FL 33032-4694
5 1105T ZULY
GRANADOS
24587 SW 109TH AVE
HOMESTEAD FL 33032-4605
6 1106T JOSE J BLANDON
RUTH M BLANDON
13055 SW 42ND ST
MIAMI FL 33175-3406
After the macro runs the same data will look like this.
1 1101T DAWN BARRETT
2 1102T MILENA
3 1103T JOAN MAIR
4 1104T DANIEL
5 1105T ZULY
6 1106T JOSE J BLANDON
The macros needs to loop until it finds an empty row. Remember that the
numbers shown on the sample 1,2,3,4,5,6 can grow up to 9999
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip 2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and
Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
