how to delete every second row 
Author Message
 how to delete every second row

I imported a text data file into Excel so now I have a structure that
looks like this:

Row 1: Good Data
Row 2: Garbage
Row 3: Garbage
Row 4: Good Data
Row 5: Garbage
Row 6: Garbage
Row 7: Good Data

etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
delete the Garbage rows and keep the good data rows.. I have a recorded
macro to get me started, but how do I make this so it does the whole sheet
automatically:

    Rows("4:5").Select
    Selection.Delete Shift:=xlUp
    Rows("5:6").Select
    Selection.Delete Shift:=xlUp
    Rows("6:7").Select
    Selection.Delete Shift:=xlUp
    Rows("7:8").Select
    Selection.Delete Shift:=xlUp

Ultimately I would love to delete any row that doesn't contain a string in
Column A, but I'll settle for the above for now!

Thanks!



Sun, 06 Nov 2005 20:22:54 GMT  
 how to delete every second row
Assume your data starts at row 3 and you would like to delete 2 rows then keep a row, delete 2
rows then keep a row etc, try the following - It will prompt you for a strating row, then ask you
how many rows to delete after every good row.  the next good row is after the deleted rows and so
on:-

Sub DelNumRows()

Dim LastRow As Long
Dim ans1 As Long
Dim ans2 As Long
Dim modans As Long
Dim r As Long

LastRow = ActiveSheet.UsedRange.Row - 1 + _
          ActiveSheet.UsedRange.Rows.Count

ans1 = InputBox("Choose a starting row")
ans2 = InputBox("How many rows to delete after each Good row")
num = ans2 + 1
modans = Abs(num - (ans1 Mod num))

Application.ScreenUpdating = False

   For r = LastRow To ans1 Step -1
      If (Rows(r).Row + modans) Mod num <> 0 Then
         Rows(r).Delete
      End If
   Next r

Application.ScreenUpdating = True
End Sub

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Chin up, and keep smiling - Life is not a rehearsal
----------------------------------------------------------------------------

Quote:

> I imported a text data file into Excel so now I have a structure that
> looks like this:

> Row 1: Good Data
> Row 2: Garbage
> Row 3: Garbage
> Row 4: Good Data
> Row 5: Garbage
> Row 6: Garbage
> Row 7: Good Data

> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
> delete the Garbage rows and keep the good data rows.. I have a recorded
> macro to get me started, but how do I make this so it does the whole sheet
> automatically:

>     Rows("4:5").Select
>     Selection.Delete Shift:=xlUp
>     Rows("5:6").Select
>     Selection.Delete Shift:=xlUp
>     Rows("6:7").Select
>     Selection.Delete Shift:=xlUp
>     Rows("7:8").Select
>     Selection.Delete Shift:=xlUp

> Ultimately I would love to delete any row that doesn't contain a string in
> Column A, but I'll settle for the above for now!

> Thanks!



Sun, 06 Nov 2005 20:32:29 GMT  
 how to delete every second row
Marcus, how about this

Sub Delete_blank()
'Will delete the whole row where there are blank cells in A1:A3000
[A1:A3000].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel '97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **


Quote:
> I imported a text data file into Excel so now I have a structure that
> looks like this:

> Row 1: Good Data
> Row 2: Garbage
> Row 3: Garbage
> Row 4: Good Data
> Row 5: Garbage
> Row 6: Garbage
> Row 7: Good Data

> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
> delete the Garbage rows and keep the good data rows.. I have a recorded
> macro to get me started, but how do I make this so it does the whole sheet
> automatically:

>     Rows("4:5").Select
>     Selection.Delete Shift:=xlUp
>     Rows("5:6").Select
>     Selection.Delete Shift:=xlUp
>     Rows("6:7").Select
>     Selection.Delete Shift:=xlUp
>     Rows("7:8").Select
>     Selection.Delete Shift:=xlUp

> Ultimately I would love to delete any row that doesn't contain a string in
> Column A, but I'll settle for the above for now!

> Thanks!



Sun, 06 Nov 2005 20:36:10 GMT  
 how to delete every second row
Marcus,

Untested, but it should work....or you can adapt it....

Place cursor is active region....

Sub ClearRows()
Dim iRows As Integer
Dim iCounter As Integer
Dim rnActiveRegion

Set rnActiveRegion = Selection.CurrentRegion

iRows = rnActiveRegion.Rows.Count

For iCounter = iRows To 1 Step -1
    If iRows Mod 3 = 2 Or iRows Mod 3 = 0 Then
        rnActiveRegion(iCounter, 1).EntireRow.Delete
    End If
Next
End Sub


Quote:
> I imported a text data file into Excel so now I have a structure that
> looks like this:

> Row 1: Good Data
> Row 2: Garbage
> Row 3: Garbage
> Row 4: Good Data
> Row 5: Garbage
> Row 6: Garbage
> Row 7: Good Data

> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
> delete the Garbage rows and keep the good data rows.. I have a recorded
> macro to get me started, but how do I make this so it does the whole sheet
> automatically:

>     Rows("4:5").Select
>     Selection.Delete Shift:=xlUp
>     Rows("5:6").Select
>     Selection.Delete Shift:=xlUp
>     Rows("6:7").Select
>     Selection.Delete Shift:=xlUp
>     Rows("7:8").Select
>     Selection.Delete Shift:=xlUp

> Ultimately I would love to delete any row that doesn't contain a string in
> Column A, but I'll settle for the above for now!

> Thanks!



Sun, 06 Nov 2005 20:38:42 GMT  
 how to delete every second row
Ken, that worked beautifully.

Now let's pretend I wanted to change it a little bit to delete any row
that doesn't contain "xml" somewhere in the A Column. Basically I want to
delete all rows that don't contain a .xml filename.

I'm wondering if I can edit your macro to do that.

Marcus

Quote:

> Assume your data starts at row 3 and you would like to delete 2 rows then keep a row, delete 2
> rows then keep a row etc, try the following - It will prompt you for a strating row, then ask you
> how many rows to delete after every good row.  the next good row is after the deleted rows and so
> on:-
> Sub DelNumRows()
> Dim LastRow As Long
> Dim ans1 As Long
> Dim ans2 As Long
> Dim modans As Long
> Dim r As Long
> LastRow = ActiveSheet.UsedRange.Row - 1 + _
>           ActiveSheet.UsedRange.Rows.Count
> ans1 = InputBox("Choose a starting row")
> ans2 = InputBox("How many rows to delete after each Good row")
> num = ans2 + 1
> modans = Abs(num - (ans1 Mod num))
> Application.ScreenUpdating = False
>    For r = LastRow To ans1 Step -1
>       If (Rows(r).Row + modans) Mod num <> 0 Then
>          Rows(r).Delete
>       End If
>    Next r
> Application.ScreenUpdating = True
> End Sub
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                    Sys Spec - Win XP Pro /  XL2K & XLXP
> ----------------------------------------------------------------------------
>   Chin up, and keep smiling - Life is not a rehearsal
> ----------------------------------------------------------------------------

>> I imported a text data file into Excel so now I have a structure that
>> looks like this:

>> Row 1: Good Data
>> Row 2: Garbage
>> Row 3: Garbage
>> Row 4: Good Data
>> Row 5: Garbage
>> Row 6: Garbage
>> Row 7: Good Data

>> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
>> delete the Garbage rows and keep the good data rows.. I have a recorded
>> macro to get me started, but how do I make this so it does the whole sheet
>> automatically:

>>     Rows("4:5").Select
>>     Selection.Delete Shift:=xlUp
>>     Rows("5:6").Select
>>     Selection.Delete Shift:=xlUp
>>     Rows("6:7").Select
>>     Selection.Delete Shift:=xlUp
>>     Rows("7:8").Select
>>     Selection.Delete Shift:=xlUp

>> Ultimately I would love to delete any row that doesn't contain a string in
>> Column A, but I'll settle for the above for now!

>> Thanks!



Mon, 07 Nov 2005 14:16:55 GMT  
 how to delete every second row
Sub DeleteRowsContaining()
Dim r As Long
Dim ans As String
Dim c As Range
Dim lrow as long

ans = InputBox("What string do you want rows to be deleted if they contain it?")
Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
       ActiveSheet.UsedRange.Rows.Count
    For r = lrow To 1 Step -1
      With Cells(r, 1)
         Set c = .Find(ans, LookIn:=xlValues)
            If Not c Is Nothing Then
              .EntireRow.Delete
            End If
      End With
 Next r
Application.ScreenUpdating = True

End Sub

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Chin up, and keep smiling - Life is not a rehearsal
----------------------------------------------------------------------------

Quote:

> Ken, that worked beautifully.

> Now let's pretend I wanted to change it a little bit to delete any row
> that doesn't contain "xml" somewhere in the A Column. Basically I want to
> delete all rows that don't contain a .xml filename.

> I'm wondering if I can edit your macro to do that.

> Marcus


> > Assume your data starts at row 3 and you would like to delete 2 rows then keep a row, delete 2
> > rows then keep a row etc, try the following - It will prompt you for a strating row, then ask
you
> > how many rows to delete after every good row.  the next good row is after the deleted rows and
so
> > on:-

> > Sub DelNumRows()

> > Dim LastRow As Long
> > Dim ans1 As Long
> > Dim ans2 As Long
> > Dim modans As Long
> > Dim r As Long

> > LastRow = ActiveSheet.UsedRange.Row - 1 + _
> >           ActiveSheet.UsedRange.Rows.Count

> > ans1 = InputBox("Choose a starting row")
> > ans2 = InputBox("How many rows to delete after each Good row")
> > num = ans2 + 1
> > modans = Abs(num - (ans1 Mod num))

> > Application.ScreenUpdating = False

> >    For r = LastRow To ans1 Step -1
> >       If (Rows(r).Row + modans) Mod num <> 0 Then
> >          Rows(r).Delete
> >       End If
> >    Next r

> > Application.ScreenUpdating = True
> > End Sub

> > --
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

> > ----------------------------------------------------------------------------
> >   Chin up, and keep smiling - Life is not a rehearsal
> > ----------------------------------------------------------------------------




- Show quoted text -

Quote:
> >> I imported a text data file into Excel so now I have a structure that
> >> looks like this:

> >> Row 1: Good Data
> >> Row 2: Garbage
> >> Row 3: Garbage
> >> Row 4: Good Data
> >> Row 5: Garbage
> >> Row 6: Garbage
> >> Row 7: Good Data

> >> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
> >> delete the Garbage rows and keep the good data rows.. I have a recorded
> >> macro to get me started, but how do I make this so it does the whole sheet
> >> automatically:

> >>     Rows("4:5").Select
> >>     Selection.Delete Shift:=xlUp
> >>     Rows("5:6").Select
> >>     Selection.Delete Shift:=xlUp
> >>     Rows("6:7").Select
> >>     Selection.Delete Shift:=xlUp
> >>     Rows("7:8").Select
> >>     Selection.Delete Shift:=xlUp

> >> Ultimately I would love to delete any row that doesn't contain a string in
> >> Column A, but I'll settle for the above for now!

> >> Thanks!



Mon, 07 Nov 2005 17:43:54 GMT  
 how to delete every second row
Perfect, except opposite. I want to delete rows that DO NOT contain a
string. Can I easily modify this one?

Marcus

Quote:

> Sub DeleteRowsContaining()
> Dim r As Long
> Dim ans As String
> Dim c As Range
> Dim lrow as long
> ans = InputBox("What string do you want rows to be deleted if they contain it?")
> Application.ScreenUpdating = False
> lrow = ActiveSheet.UsedRange.Row - 1 + _
>        ActiveSheet.UsedRange.Rows.Count
>     For r = lrow To 1 Step -1
>       With Cells(r, 1)
>          Set c = .Find(ans, LookIn:=xlValues)
>             If Not c Is Nothing Then
>               .EntireRow.Delete
>             End If
>       End With
>  Next r
> Application.ScreenUpdating = True
> End Sub
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                    Sys Spec - Win XP Pro /  XL2K & XLXP
> ----------------------------------------------------------------------------
>   Chin up, and keep smiling - Life is not a rehearsal
> ----------------------------------------------------------------------------

>> Ken, that worked beautifully.

>> Now let's pretend I wanted to change it a little bit to delete any row
>> that doesn't contain "xml" somewhere in the A Column. Basically I want to
>> delete all rows that don't contain a .xml filename.

>> I'm wondering if I can edit your macro to do that.

>> Marcus


>> > Assume your data starts at row 3 and you would like to delete 2 rows then keep a row, delete 2
>> > rows then keep a row etc, try the following - It will prompt you for a strating row, then ask
> you
>> > how many rows to delete after every good row.  the next good row is after the deleted rows and
> so
>> > on:-

>> > Sub DelNumRows()

>> > Dim LastRow As Long
>> > Dim ans1 As Long
>> > Dim ans2 As Long
>> > Dim modans As Long
>> > Dim r As Long

>> > LastRow = ActiveSheet.UsedRange.Row - 1 + _
>> >           ActiveSheet.UsedRange.Rows.Count

>> > ans1 = InputBox("Choose a starting row")
>> > ans2 = InputBox("How many rows to delete after each Good row")
>> > num = ans2 + 1
>> > modans = Abs(num - (ans1 Mod num))

>> > Application.ScreenUpdating = False

>> >    For r = LastRow To ans1 Step -1
>> >       If (Rows(r).Row + modans) Mod num <> 0 Then
>> >          Rows(r).Delete
>> >       End If
>> >    Next r

>> > Application.ScreenUpdating = True
>> > End Sub

>> > --
>> > Regards
>> >            Ken.......................    Microsoft MVP - Excel
>> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

>> > ----------------------------------------------------------------------------
>> >   Chin up, and keep smiling - Life is not a rehearsal
>> > ----------------------------------------------------------------------------



>> >> I imported a text data file into Excel so now I have a structure that
>> >> looks like this:

>> >> Row 1: Good Data
>> >> Row 2: Garbage
>> >> Row 3: Garbage
>> >> Row 4: Good Data
>> >> Row 5: Garbage
>> >> Row 6: Garbage
>> >> Row 7: Good Data

>> >> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
>> >> delete the Garbage rows and keep the good data rows.. I have a recorded
>> >> macro to get me started, but how do I make this so it does the whole sheet
>> >> automatically:

>> >>     Rows("4:5").Select
>> >>     Selection.Delete Shift:=xlUp
>> >>     Rows("5:6").Select
>> >>     Selection.Delete Shift:=xlUp
>> >>     Rows("6:7").Select
>> >>     Selection.Delete Shift:=xlUp
>> >>     Rows("7:8").Select
>> >>     Selection.Delete Shift:=xlUp

>> >> Ultimately I would love to delete any row that doesn't contain a string in
>> >> Column A, but I'll settle for the above for now!

>> >> Thanks!



Mon, 07 Nov 2005 19:31:57 GMT  
 how to delete every second row
Yep - Just put 'not' in the msgbox text so as not to confuse, and then take out the 'Not' from 'If
Not c is nothing' .

Sub DeleteRowsContaining()
Dim r As Long
Dim ans As String
Dim c As Range
Dim lrow As Long

ans = InputBox("What string do you not want rows to be deleted if they contain it?")
Application.ScreenUpdating = False

lrow = ActiveSheet.UsedRange.Row - 1 + _
       ActiveSheet.UsedRange.Rows.Count
    For r = lrow To 1 Step -1
      With Cells(r, 1)
         Set c = .Find(ans, LookIn:=xlValues)
            If c Is Nothing Then
              .EntireRow.Delete
            End If
      End With
 Next r
Application.ScreenUpdating = True

End Sub

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Chin up, and keep smiling - Life is not a rehearsal
----------------------------------------------------------------------------

Quote:

> Perfect, except opposite. I want to delete rows that DO NOT contain a
> string. Can I easily modify this one?

> Marcus


> > Sub DeleteRowsContaining()
> > Dim r As Long
> > Dim ans As String
> > Dim c As Range
> > Dim lrow as long

> > ans = InputBox("What string do you want rows to be deleted if they contain it?")
> > Application.ScreenUpdating = False

> > lrow = ActiveSheet.UsedRange.Row - 1 + _
> >        ActiveSheet.UsedRange.Rows.Count
> >     For r = lrow To 1 Step -1
> >       With Cells(r, 1)
> >          Set c = .Find(ans, LookIn:=xlValues)
> >             If Not c Is Nothing Then
> >               .EntireRow.Delete
> >             End If
> >       End With
> >  Next r
> > Application.ScreenUpdating = True

> > End Sub

> > --
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

> > ----------------------------------------------------------------------------
> >   Chin up, and keep smiling - Life is not a rehearsal
> > ----------------------------------------------------------------------------




- Show quoted text -

Quote:
> >> Ken, that worked beautifully.

> >> Now let's pretend I wanted to change it a little bit to delete any row
> >> that doesn't contain "xml" somewhere in the A Column. Basically I want to
> >> delete all rows that don't contain a .xml filename.

> >> I'm wondering if I can edit your macro to do that.

> >> Marcus


> >> > Assume your data starts at row 3 and you would like to delete 2 rows then keep a row,
delete 2
> >> > rows then keep a row etc, try the following - It will prompt you for a strating row, then
ask
> > you
> >> > how many rows to delete after every good row.  the next good row is after the deleted rows
and
> > so
> >> > on:-

> >> > Sub DelNumRows()

> >> > Dim LastRow As Long
> >> > Dim ans1 As Long
> >> > Dim ans2 As Long
> >> > Dim modans As Long
> >> > Dim r As Long

> >> > LastRow = ActiveSheet.UsedRange.Row - 1 + _
> >> >           ActiveSheet.UsedRange.Rows.Count

> >> > ans1 = InputBox("Choose a starting row")
> >> > ans2 = InputBox("How many rows to delete after each Good row")
> >> > num = ans2 + 1
> >> > modans = Abs(num - (ans1 Mod num))

> >> > Application.ScreenUpdating = False

> >> >    For r = LastRow To ans1 Step -1
> >> >       If (Rows(r).Row + modans) Mod num <> 0 Then
> >> >          Rows(r).Delete
> >> >       End If
> >> >    Next r

> >> > Application.ScreenUpdating = True
> >> > End Sub

> >> > --
> >> > Regards
> >> >            Ken.......................    Microsoft MVP - Excel
> >> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

> >> > ----------------------------------------------------------------------------
> >> >   Chin up, and keep smiling - Life is not a rehearsal
> >> > ----------------------------------------------------------------------------



> >> >> I imported a text data file into Excel so now I have a structure that
> >> >> looks like this:

> >> >> Row 1: Good Data
> >> >> Row 2: Garbage
> >> >> Row 3: Garbage
> >> >> Row 4: Good Data
> >> >> Row 5: Garbage
> >> >> Row 6: Garbage
> >> >> Row 7: Good Data

> >> >> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
> >> >> delete the Garbage rows and keep the good data rows.. I have a recorded
> >> >> macro to get me started, but how do I make this so it does the whole sheet
> >> >> automatically:

> >> >>     Rows("4:5").Select
> >> >>     Selection.Delete Shift:=xlUp
> >> >>     Rows("5:6").Select
> >> >>     Selection.Delete Shift:=xlUp
> >> >>     Rows("6:7").Select
> >> >>     Selection.Delete Shift:=xlUp
> >> >>     Rows("7:8").Select
> >> >>     Selection.Delete Shift:=xlUp

> >> >> Ultimately I would love to delete any row that doesn't contain a string in
> >> >> Column A, but I'll settle for the above for now!

> >> >> Thanks!



Mon, 07 Nov 2005 19:44:43 GMT  
 how to delete every second row
Very very nice! It worked like a charm, and quickly too!

Great code.

Does anyone know if you can do a COUNTIF that checks for a string? Like in
English: Count if the cell has "xml" somewhere in there.

It seems the way COUNTIF works by default is it checks ONLY for that
string.

Marcus

Quote:

> Yep - Just put 'not' in the msgbox text so as not to confuse, and then take out the 'Not' from 'If
> Not c is nothing' .
> Sub DeleteRowsContaining()
> Dim r As Long
> Dim ans As String
> Dim c As Range
> Dim lrow As Long
> ans = InputBox("What string do you not want rows to be deleted if they contain it?")
> Application.ScreenUpdating = False
> lrow = ActiveSheet.UsedRange.Row - 1 + _
>        ActiveSheet.UsedRange.Rows.Count
>     For r = lrow To 1 Step -1
>       With Cells(r, 1)
>          Set c = .Find(ans, LookIn:=xlValues)
>             If c Is Nothing Then
>               .EntireRow.Delete
>             End If
>       End With
>  Next r
> Application.ScreenUpdating = True
> End Sub
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                    Sys Spec - Win XP Pro /  XL2K & XLXP
> ----------------------------------------------------------------------------
>   Chin up, and keep smiling - Life is not a rehearsal
> ----------------------------------------------------------------------------

>> Perfect, except opposite. I want to delete rows that DO NOT contain a
>> string. Can I easily modify this one?

>> Marcus


>> > Sub DeleteRowsContaining()
>> > Dim r As Long
>> > Dim ans As String
>> > Dim c As Range
>> > Dim lrow as long

>> > ans = InputBox("What string do you want rows to be deleted if they contain it?")
>> > Application.ScreenUpdating = False

>> > lrow = ActiveSheet.UsedRange.Row - 1 + _
>> >        ActiveSheet.UsedRange.Rows.Count
>> >     For r = lrow To 1 Step -1
>> >       With Cells(r, 1)
>> >          Set c = .Find(ans, LookIn:=xlValues)
>> >             If Not c Is Nothing Then
>> >               .EntireRow.Delete
>> >             End If
>> >       End With
>> >  Next r
>> > Application.ScreenUpdating = True

>> > End Sub

>> > --
>> > Regards
>> >            Ken.......................    Microsoft MVP - Excel
>> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

>> > ----------------------------------------------------------------------------
>> >   Chin up, and keep smiling - Life is not a rehearsal
>> > ----------------------------------------------------------------------------



>> >> Ken, that worked beautifully.

>> >> Now let's pretend I wanted to change it a little bit to delete any row
>> >> that doesn't contain "xml" somewhere in the A Column. Basically I want to
>> >> delete all rows that don't contain a .xml filename.

>> >> I'm wondering if I can edit your macro to do that.

>> >> Marcus


>> >> > Assume your data starts at row 3 and you would like to delete 2 rows then keep a row,
> delete 2
>> >> > rows then keep a row etc, try the following - It will prompt you for a strating row, then
> ask
>> > you
>> >> > how many rows to delete after every good row.  the next good row is after the deleted rows
> and
>> > so
>> >> > on:-

>> >> > Sub DelNumRows()

>> >> > Dim LastRow As Long
>> >> > Dim ans1 As Long
>> >> > Dim ans2 As Long
>> >> > Dim modans As Long
>> >> > Dim r As Long

>> >> > LastRow = ActiveSheet.UsedRange.Row - 1 + _
>> >> >           ActiveSheet.UsedRange.Rows.Count

>> >> > ans1 = InputBox("Choose a starting row")
>> >> > ans2 = InputBox("How many rows to delete after each Good row")
>> >> > num = ans2 + 1
>> >> > modans = Abs(num - (ans1 Mod num))

>> >> > Application.ScreenUpdating = False

>> >> >    For r = LastRow To ans1 Step -1
>> >> >       If (Rows(r).Row + modans) Mod num <> 0 Then
>> >> >          Rows(r).Delete
>> >> >       End If
>> >> >    Next r

>> >> > Application.ScreenUpdating = True
>> >> > End Sub

>> >> > --
>> >> > Regards
>> >> >            Ken.......................    Microsoft MVP - Excel
>> >> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

>> >> > ----------------------------------------------------------------------------
>> >> >   Chin up, and keep smiling - Life is not a rehearsal
>> >> > ----------------------------------------------------------------------------



>> >> >> I imported a text data file into Excel so now I have a structure that
>> >> >> looks like this:

>> >> >> Row 1: Good Data
>> >> >> Row 2: Garbage
>> >> >> Row 3: Garbage
>> >> >> Row 4: Good Data
>> >> >> Row 5: Garbage
>> >> >> Row 6: Garbage
>> >> >> Row 7: Good Data

>> >> >> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
>> >> >> delete the Garbage rows and keep the good data rows.. I have a recorded
>> >> >> macro to get me started, but how do I make this so it does the whole sheet
>> >> >> automatically:

>> >> >>     Rows("4:5").Select
>> >> >>     Selection.Delete Shift:=xlUp
>> >> >>     Rows("5:6").Select
>> >> >>     Selection.Delete Shift:=xlUp
>> >> >>     Rows("6:7").Select
>> >> >>     Selection.Delete Shift:=xlUp
>> >> >>     Rows("7:8").Select
>> >> >>     Selection.Delete Shift:=xlUp

>> >> >> Ultimately I would love to delete any row that doesn't contain a string in
>> >> >> Column A, but I'll settle for the above for now!

>> >> >> Thanks!



Mon, 07 Nov 2005 20:05:48 GMT  
 how to delete every second row
=COUNTIF(A1:A100,"*xml*")

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Chin up, and keep smiling - Life is not a rehearsal
----------------------------------------------------------------------------

Quote:

> Very very nice! It worked like a charm, and quickly too!

> Great code.

> Does anyone know if you can do a COUNTIF that checks for a string? Like in
> English: Count if the cell has "xml" somewhere in there.

> It seems the way COUNTIF works by default is it checks ONLY for that
> string.

> Marcus


> > Yep - Just put 'not' in the msgbox text so as not to confuse, and then take out the 'Not' from
'If
> > Not c is nothing' .

> > Sub DeleteRowsContaining()
> > Dim r As Long
> > Dim ans As String
> > Dim c As Range
> > Dim lrow As Long

> > ans = InputBox("What string do you not want rows to be deleted if they contain it?")
> > Application.ScreenUpdating = False

> > lrow = ActiveSheet.UsedRange.Row - 1 + _
> >        ActiveSheet.UsedRange.Rows.Count
> >     For r = lrow To 1 Step -1
> >       With Cells(r, 1)
> >          Set c = .Find(ans, LookIn:=xlValues)
> >             If c Is Nothing Then
> >               .EntireRow.Delete
> >             End If
> >       End With
> >  Next r
> > Application.ScreenUpdating = True

> > End Sub

> > --
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

> > ----------------------------------------------------------------------------
> >   Chin up, and keep smiling - Life is not a rehearsal
> > ----------------------------------------------------------------------------




- Show quoted text -

Quote:
> >> Perfect, except opposite. I want to delete rows that DO NOT contain a
> >> string. Can I easily modify this one?

> >> Marcus


> >> > Sub DeleteRowsContaining()
> >> > Dim r As Long
> >> > Dim ans As String
> >> > Dim c As Range
> >> > Dim lrow as long

> >> > ans = InputBox("What string do you want rows to be deleted if they contain it?")
> >> > Application.ScreenUpdating = False

> >> > lrow = ActiveSheet.UsedRange.Row - 1 + _
> >> >        ActiveSheet.UsedRange.Rows.Count
> >> >     For r = lrow To 1 Step -1
> >> >       With Cells(r, 1)
> >> >          Set c = .Find(ans, LookIn:=xlValues)
> >> >             If Not c Is Nothing Then
> >> >               .EntireRow.Delete
> >> >             End If
> >> >       End With
> >> >  Next r
> >> > Application.ScreenUpdating = True

> >> > End Sub

> >> > --
> >> > Regards
> >> >            Ken.......................    Microsoft MVP - Excel
> >> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

> >> > ----------------------------------------------------------------------------
> >> >   Chin up, and keep smiling - Life is not a rehearsal
> >> > ----------------------------------------------------------------------------



> >> >> Ken, that worked beautifully.

> >> >> Now let's pretend I wanted to change it a little bit to delete any row
> >> >> that doesn't contain "xml" somewhere in the A Column. Basically I want to
> >> >> delete all rows that don't contain a .xml filename.

> >> >> I'm wondering if I can edit your macro to do that.

> >> >> Marcus


> >> >> > Assume your data starts at row 3 and you would like to delete 2 rows then keep a row,
> > delete 2
> >> >> > rows then keep a row etc, try the following - It will prompt you for a strating row,
then
> > ask
> >> > you
> >> >> > how many rows to delete after every good row.  the next good row is after the deleted
rows
> > and
> >> > so
> >> >> > on:-

> >> >> > Sub DelNumRows()

> >> >> > Dim LastRow As Long
> >> >> > Dim ans1 As Long
> >> >> > Dim ans2 As Long
> >> >> > Dim modans As Long
> >> >> > Dim r As Long

> >> >> > LastRow = ActiveSheet.UsedRange.Row - 1 + _
> >> >> >           ActiveSheet.UsedRange.Rows.Count

> >> >> > ans1 = InputBox("Choose a starting row")
> >> >> > ans2 = InputBox("How many rows to delete after each Good row")
> >> >> > num = ans2 + 1
> >> >> > modans = Abs(num - (ans1 Mod num))

> >> >> > Application.ScreenUpdating = False

> >> >> >    For r = LastRow To ans1 Step -1
> >> >> >       If (Rows(r).Row + modans) Mod num <> 0 Then
> >> >> >          Rows(r).Delete
> >> >> >       End If
> >> >> >    Next r

> >> >> > Application.ScreenUpdating = True
> >> >> > End Sub

> >> >> > --
> >> >> > Regards
> >> >> >            Ken.......................    Microsoft MVP - Excel
> >> >> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

> >> >> > ----------------------------------------------------------------------------
> >> >> >   Chin up, and keep smiling - Life is not a rehearsal
> >> >> > ----------------------------------------------------------------------------



> >> >> >> I imported a text data file into Excel so now I have a structure that
> >> >> >> looks like this:

> >> >> >> Row 1: Good Data
> >> >> >> Row 2: Garbage
> >> >> >> Row 3: Garbage
> >> >> >> Row 4: Good Data
> >> >> >> Row 5: Garbage
> >> >> >> Row 6: Garbage
> >> >> >> Row 7: Good Data

> >> >> >> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
> >> >> >> delete the Garbage rows and keep the good data rows.. I have a recorded
> >> >> >> macro to get me started, but how do I make this so it does the whole sheet
> >> >> >> automatically:

> >> >> >>     Rows("4:5").Select
> >> >> >>     Selection.Delete Shift:=xlUp
> >> >> >>     Rows("5:6").Select
> >> >> >>     Selection.Delete Shift:=xlUp
> >> >> >>     Rows("6:7").Select
> >> >> >>     Selection.Delete Shift:=xlUp
> >> >> >>     Rows("7:8").Select
> >> >> >>     Selection.Delete Shift:=xlUp

> >> >> >> Ultimately I would love to delete any row that doesn't contain a string in
> >> >> >> Column A, but I'll settle for the above for now!

> >> >> >> Thanks!



Mon, 07 Nov 2005 23:45:56 GMT  
 how to delete every second row
COUNTIF will accept wild cards, i.e. =COUNTIF(A1:A100,"*xlm*")

Quote:

>Very very nice! It worked like a charm, and quickly too!

>Great code.

>Does anyone know if you can do a COUNTIF that checks for a string? Like in
>English: Count if the cell has "xml" somewhere in there.

>It seems the way COUNTIF works by default is it checks ONLY for that
>string.

>Marcus


>> Yep - Just put 'not' in the msgbox text so as not to confuse, and then take out the 'Not'
from 'If
>> Not c is nothing' .

>> Sub DeleteRowsContaining()
>> Dim r As Long
>> Dim ans As String
>> Dim c As Range
>> Dim lrow As Long

>> ans = InputBox("What string do you not want rows to be deleted if they contain it?")
>> Application.ScreenUpdating = False

>> lrow = ActiveSheet.UsedRange.Row - 1 + _
>>        ActiveSheet.UsedRange.Rows.Count
>>     For r = lrow To 1 Step -1
>>       With Cells(r, 1)
>>          Set c = .Find(ans, LookIn:=xlValues)
>>             If c Is Nothing Then
>>               .EntireRow.Delete
>>             End If
>>       End With
>>  Next r
>> Application.ScreenUpdating = True

>> End Sub

>> --
>> Regards
>>            Ken.......................    Microsoft MVP - Excel
>>                    Sys Spec - Win XP Pro /  XL2K & XLXP

>> ----------------------------------------------------------------------------
>>   Chin up, and keep smiling - Life is not a rehearsal
>> ----------------------------------------------------------------------------




- Show quoted text -

Quote:
>>> Perfect, except opposite. I want to delete rows that DO NOT contain a
>>> string. Can I easily modify this one?

>>> Marcus


>>> > Sub DeleteRowsContaining()
>>> > Dim r As Long
>>> > Dim ans As String
>>> > Dim c As Range
>>> > Dim lrow as long

>>> > ans = InputBox("What string do you want rows to be deleted if they contain it?")
>>> > Application.ScreenUpdating = False

>>> > lrow = ActiveSheet.UsedRange.Row - 1 + _
>>> >        ActiveSheet.UsedRange.Rows.Count
>>> >     For r = lrow To 1 Step -1
>>> >       With Cells(r, 1)
>>> >          Set c = .Find(ans, LookIn:=xlValues)
>>> >             If Not c Is Nothing Then
>>> >               .EntireRow.Delete
>>> >             End If
>>> >       End With
>>> >  Next r
>>> > Application.ScreenUpdating = True

>>> > End Sub

>>> > --
>>> > Regards
>>> >            Ken.......................    Microsoft MVP - Excel
>>> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

>>> > ----------------------------------------------------------------------------
>>> >   Chin up, and keep smiling - Life is not a rehearsal
>>> > ----------------------------------------------------------------------------



>>> >> Ken, that worked beautifully.

>>> >> Now let's pretend I wanted to change it a little bit to delete any row
>>> >> that doesn't contain "xml" somewhere in the A Column. Basically I want to
>>> >> delete all rows that don't contain a .xml filename.

>>> >> I'm wondering if I can edit your macro to do that.

>>> >> Marcus


>>> >> > Assume your data starts at row 3 and you would like to delete 2 rows then keep a row,
>> delete 2
>>> >> > rows then keep a row etc, try the following - It will prompt you for a strating row,
then
>> ask
>>> > you
>>> >> > how many rows to delete after every good row.  the next good row is after the deleted
rows
>> and
>>> > so
>>> >> > on:-

>>> >> > Sub DelNumRows()

>>> >> > Dim LastRow As Long
>>> >> > Dim ans1 As Long
>>> >> > Dim ans2 As Long
>>> >> > Dim modans As Long
>>> >> > Dim r As Long

>>> >> > LastRow = ActiveSheet.UsedRange.Row - 1 + _
>>> >> >           ActiveSheet.UsedRange.Rows.Count

>>> >> > ans1 = InputBox("Choose a starting row")
>>> >> > ans2 = InputBox("How many rows to delete after each Good row")
>>> >> > num = ans2 + 1
>>> >> > modans = Abs(num - (ans1 Mod num))

>>> >> > Application.ScreenUpdating = False

>>> >> >    For r = LastRow To ans1 Step -1
>>> >> >       If (Rows(r).Row + modans) Mod num <> 0 Then
>>> >> >          Rows(r).Delete
>>> >> >       End If
>>> >> >    Next r

>>> >> > Application.ScreenUpdating = True
>>> >> > End Sub

>>> >> > --
>>> >> > Regards
>>> >> >            Ken.......................    Microsoft MVP - Excel
>>> >> >                    Sys Spec - Win XP Pro /  XL2K & XLXP

>>> >> > ----------------------------------------------------------------------------
>>> >> >   Chin up, and keep smiling - Life is not a rehearsal
>>> >> > ----------------------------------------------------------------------------



>>> >> >> I imported a text data file into Excel so now I have a structure that
>>> >> >> looks like this:

>>> >> >> Row 1: Good Data
>>> >> >> Row 2: Garbage
>>> >> >> Row 3: Garbage
>>> >> >> Row 4: Good Data
>>> >> >> Row 5: Garbage
>>> >> >> Row 6: Garbage
>>> >> >> Row 7: Good Data

>>> >> >> etc. etc. etc. throughout the whole thing (about 3000 lines). How can I
>>> >> >> delete the Garbage rows and keep the good data rows.. I have a recorded
>>> >> >> macro to get me started, but how do I make this so it does the whole sheet
>>> >> >> automatically:

>>> >> >>     Rows("4:5").Select
>>> >> >>     Selection.Delete Shift:=xlUp
>>> >> >>     Rows("5:6").Select
>>> >> >>     Selection.Delete Shift:=xlUp
>>> >> >>     Rows("6:7").Select
>>> >> >>     Selection.Delete Shift:=xlUp
>>> >> >>     Rows("7:8").Select
>>> >> >>     Selection.Delete Shift:=xlUp

>>> >> >> Ultimately I would love to delete any row that doesn't contain a string in
>>> >> >> Column A, but I'll settle for the above for now!

>>> >> >> Thanks!



Mon, 07 Nov 2005 23:52:50 GMT  
 
 [ 11 post ] 

 Relevant Pages 

1. Please - Macro that deletes every second row

2. Deleting every second row?

3. delete every second row

4. Deleting every second row

5. Getting #deleted message in every field of every row when linking to a SQL Server DB

6. Deleting several rows every nth row

7. duplicating every second row

8. inserting a row to every second line.

9. re : choose every second row

10. adding every second cell in a row


 
Powered by phpBB® Forum Software © phpBB Group