Selecting every Nth line of data

[BruceEdwards]

I have a need to strip out selected rows from a spreadsheet, based on the number of rows of data.

Example:

If there are 10 rows of data, I want to keep all of them.
If there are 20 rows of data, I want to keep the first row and every third row thereafter - so that would be rows 1,4,7, etc
If there are 50 rows, first row and every 7th row, so 1,8,15, etc
and so forth.

So a macro to do that and delete all the other rows is what I need.


Thanks for any help.

 

[Rick Rothstein]

I have a need to strip out selected rows from a spreadsheet, based on the number of rows of data.

Example:

If there are 10 rows of data, I want to keep all of them.
If there are 20 rows of data, I want to keep the first row and every third row thereafter - so that would be rows 1,4,7, etc
If there are 50 rows, first row and every 7th row, so 1,8,15, etc
and so forth.

So a macro to do that and delete all the other rows is what I need.

You are going to have to provide more details about the "and so forth" as I do not see the progression between 10, 20, 50 nor 0, 3, 7 that would allow me to extend those numbers for higher values.

 

[BruceEdwards]

Thanks for the reply...you are correct, the "and so forth" was a poor statement. Really what I was after is if someone could get me to that point, I think I would be able to take it from there.

 

[Rick Rothstein]

Thanks for the reply...you are correct, the "and so forth" was a poor statement. Really what I was after is if someone could get me to that point, I think I would be able to take it from there.

Okay, but you still have to clarify your ranges for us. You wrote this...

If there are 10 rows of data, I want to keep all of them.
If there are 20 rows of data, I want to keep the first row and every third row thereafter - so that would be rows 1,4,7, etc
If there are 50 rows, first row and every 7th row, so 1,8,15, etc
and so forth.

What should happen in between (for row counts of 15, 35, 51, and such.)?

 

[BruceEdwards]

Thanks for forcing clarity on me...my original post now seems kind of lame LOL.

If there are 1 to10 rows of data, I want to keep all of them.
If there are 11 to 20 rows of data, I want to keep the first row and every third row thereafter - so that would be rows 1,4,7, etc, and delete all others
If there are 21 to 50 rows, first row and every 7th row, so 1,8,15, etc and delete all others

All of the "non-selected" rows would be deleted

 

[Rick Rothstein]

If there are 1 to10 rows of data, I want to keep all of them.
If there are 11 to 20 rows of data, I want to keep the first row and every third row thereafter - so that would be rows 1,4,7, etc, and delete all others
If there are 21 to 50 rows, first row and every 7th row, so 1,8,15, etc and delete all others

I believe this should do what you want...

[table="width: 500"]
[tr]
	[td]Sub ThinoutTheNumberOfRows()
  Dim LastRow As Long, SkipAmount As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  SkipAmount = Evaluate("LOOKUP(" & LastRow & ",{0,11,21},{0,3,7})")
  If SkipAmount Then
    Range("A1:A" & LastRow) = Evaluate(Replace("IF(MOD(ROW(A1:A@)-1," & SkipAmount & "),""#N/A"",A1:A@)", "@", LastRow))
    Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End If
End Sub[/td]
[/tr]
[/table]

As written, the code will delete all but every 7th row when there are more than 20 rows. To specify a different delete rate when the number of rows are greater than 50, add 51 to the first series and the new skip rate to the second series.

 

[BruceEdwards]

Perfect! Thanks very much