I uploaded file "sample_sheet.xls" for review. When I tried the script it didn't work and I get an error
On Nov 6, 6:59 am, "Daniel" <dcolarde...@free.fr> wrote: > 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/exceldailytip2. Join our LinkedIN group > @http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com5. Excel Tips and > Tricks athttp://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below > linkhttp://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&...- > Hide quoted text - > > - Show quoted text - -- ---------------------------------------------------------------------------------- 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
