Thank you very much, this worked great !!!
Thank you, Thank you, Thank you!!
On Sat, Nov 6, 2010 at 11:36 AM, Swapnil Palande <swapnilp1...@gmail.com>wrote:
> Hi,
>
> Pls find attached file for solution.
>
> Following is macro I have developed:
>
> Sub Delete_Rows()
> Dim rng As Range, counter As Integer
> Set rng = Sheets(1).Range("A1")
> counter = 1
>
> Do While rng.Value <> ""
> If InStr(1, rng.Value, " ") <> 0 Then
> If IsNumeric(Left(Trim(rng.Value), InStr(1, Trim(rng.Value), " ",
> vbTextCompare) - 1)) Then
> If CInt(Left(Trim(rng.Value), InStr(1, Trim(rng.Value), " ",
> vbTextCompare) - 1)) = counter Then
> counter = counter + 1
> Else
> Set rng = rng.Offset(-1, 0)
> rng.Offset(1, 0).EntireRow.Delete
> End If
> Else
> Set rng = rng.Offset(-1, 0)
> rng.Offset(1, 0).EntireRow.Delete
> End If
> Else
> Set rng = rng.Offset(-1, 0)
> rng.Offset(1, 0).EntireRow.Delete
> End If
> Set rng = rng.Offset(1, 0)
> Loop
>
> End Sub
>
> Regards,
> Swapnil.
>
> On Sat, Nov 6, 2010 at 6:35 PM, Cesar Delanoval <cdelano...@gmail.com>wrote:
>
>> Thank you for your reply. I have enclosed the sample sheet with the
>> data. When I run the scrpit I get an error on the first line of code.
>>
>> I colored red the first identifier which will tell you which row to keep
>> (rows starting with 1, 2, 3, etc) I need the other rows deleted
>>
>> On Sat, Nov 6, 2010 at 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/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
>>>
>>
>> --
>> ----------------------------------------------------------------------------------
>>
>>
>> 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
>
--
----------------------------------------------------------------------------------
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
