take from every 10 rows the last one

P

peter_walsh_

Hello,

I have a worksheet with 20 000 rows. These are too many rows. Therefor
I want to take from every 10 rows only the last row.
(So as a result there will be 2000 rows)

Any Idea?

Thanks in advance
 
J

Jan Karel Pieterse

Hi Peter_walsh_,
I have a worksheet with 20 000 rows. These are too many rows. Therefore
I want to take from every 10 rows only the last row.
(So as a result there will be 2000 rows)
Click to expand...

In any adjacent column, paste this formula:

=MOD(ROW(),10)+0.00001*ROW()

Copy alongside the data

Now copy that column and paste-special, values on top of itself. Then
sort the entire datarange on this column.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
F

Frank Kabel

Hi
try the following macro (make a backup of your data prior
to running this):

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
if RowNdx mod 10 <>0 then
Rows(RowNdx).Delete
end if
Next RowNdx
Application.ScreenUpdating = True
End Sub
 
Top