Deleting every 2nd and 3rd row 
Author Message
 Deleting every 2nd and 3rd row

How do I delete every 2nd and 3rd row  (i.e. rows 2,3 and  5,6 and 8,9 and
11,12, ...etc.) from a large spreadsheet?

The method I am presently using is that I copy the cells from rows I wish to
keep ( rows 1, 4, 7, ...etc.) into empty cells located at the right of the
current sheet by copying the cell of the first row and 2 blank cells below
and drag paste downwards to end of worksheet, the I copy/paste into a word
document, convert table to text, do a find ^p^p^p replace ^p to remove the
blank lines the I re-import this information back into Excel.
This works but is tedious.
Ideas anyone?



Fri, 13 Apr 2001 03:00:00 GMT  
 Deleting every 2nd and 3rd row
Terry,
Try the following macro

Sub DeleteEvery()
Dim Ndx As Integer
With ActiveSheet
For Ndx = Int((.UsedRange.Rows.Count + 1) / 3) * 3 + 1 To 4 Step -3
    .Rows(Ndx - 1).Delete
    .Rows(Ndx - 2).Delete
Next Ndx
End With
End Sub

This will always always keep rows 1, 4, 7, 10 ...... regardless of how
many rows there are. There must some data of some sort in row 1.

Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel.htm

Quote:

>How do I delete every 2nd and 3rd row  (i.e. rows 2,3 and  5,6 and
8,9 and
>11,12, ...etc.) from a large spreadsheet?

>The method I am presently using is that I copy the cells from rows I
wish to
>keep ( rows 1, 4, 7, ...etc.) into empty cells located at the right
of the
>current sheet by copying the cell of the first row and 2 blank cells
below
>and drag paste downwards to end of worksheet, the I copy/paste into a
word
>document, convert table to text, do a find ^p^p^p replace ^p to
remove the
>blank lines the I re-import this information back into Excel.
>This works but is tedious.
>Ideas anyone?



Fri, 13 Apr 2001 03:00:00 GMT  
 Deleting every 2nd and 3rd row
This should work, although there may very well be a better way with less
code.

Sub DeleteEverySecondAndThirdRow()
    Application.ScreenUpdating = False
    numRows = ActiveSheet.UsedRange.Rows.Count
    For rowNum = numRows To 1 Step -1
        If rowNum Mod 3 <> 1 Then
            ActiveSheet.Rows(rowNum).EntireRow.Delete shift:=xlUp
        End If
    Next rowNum
    Application.ScreenUpdating = True
End Sub

Regards,
L. Eric Galvez
--
To reply by e-mail, remove "IDetestSPAM"



How do I delete every 2nd and 3rd row  (i.e. rows 2,3 and  5,6 and 8,9 and
11,12, ...etc.) from a large spreadsheet?

The method I am presently using is that I copy the cells from rows I wish
to
keep ( rows 1, 4, 7, ...etc.) into empty cells located at the right of the
current sheet by copying the cell of the first row and 2 blank cells below
and drag paste downwards to end of worksheet, the I copy/paste into a word
document, convert table to text, do a find ^p^p^p replace ^p to remove the
blank lines the I re-import this information back into Excel.
This works but is tedious.
Ideas anyone?



Fri, 13 Apr 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Delete every 2nd and 3rd row- VBA

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

3. Sorting every 3rd row

4. Selecting every 3rd row for copying

5. Deleting several rows every nth row

6. Dynamic Sum (of every 2nd row) on Range Resize

7. Every 2nd row bold, how to?

8. how to delete every second row

9. Deleting every second row?

10. Please - Macro that deletes every second row


 
Powered by phpBB® Forum Software © phpBB Group