Deleting Rows to compress data

Leal72,

This might work for you. I wrote this for someone else wanting to do something similar so I have tried to modify it to meet you situation.

Here are a few things you need to keep in mind.

1. It starts from the bottom and works its way up. This is the easiest way to keep track of the rows when you are deleting rows.

2. It assumes that the last row is data you want to keep. If this is not the case, some modifications are going to need to be made.

Sub aaa()

Dim lRow As Long, lLastRow As Long, lCnt As Long
Dim wsSht As Worksheet

Set wsSht = Sheets("Sheet1")

lLastRow = wsSht.Cells(Rows.Count, "A").End(xlUp).Row
lCnt = 10

For lRow = lLastRow To 1 Step -1
    If lCnt < 10 Then
        wsSht.Range("A" & lRow).EntireRow.Delete
        lCnt = lCnt + 1
    Else
        lCnt = 1
    End If

Next lRow
End Sub

Try it and see if it does what you are wanting. You may have to make some modification. If you need more help, let me know and I will see what I can do.

Originally Posted by Leal72

I've been searching through but no luck finding what I need.

I have data files with over 8000 rows of data, I don't need all the data. I want to keep every 10th row begining with row 9. Rows 1 through 8 have header information.

1.header info
2.header info
3.header info
4.header info
5.header info
6.header info
7.header info
8.header info
9.keep data
10.delete
11.delete
12.delete
13.delete
14.delete
15.delete
16.delete
17.delete
18.delete
19.keep data
20.delete
21.delete
22.delete
23.delete
24.delete
25.delete
26.delete
27.delete
28.delete
29.keep data
30.delete
and so on

One quick way without going for macro is take an unused column. Enter formula =Mod(Row(),10) in all the cells of this column dow to the last row of the list. It will give 1 in row 1, 2 in row 2 etc and 0 in row 10. This pattern will repeat. Thus rows 9,19,29,... will have 9 in the column. Switch on autofilter. Use "not equal to 9 " param of autofilter on the column. It will display all rows except 9,19,29 etc. Select the whole sheet ( Control A ) and delete all rows. Switch off autofilter and you are done!
Are you sure that you want to delete all those non-10 rows? Why not just hide them by using filter equal to 9?

Tip: to quickly populate all the cells in the unused column with our formula, select a column adjacent to the used column ( which has data in all cells to the last cell of your list ). Enter the formula in the first cell of the column. Take the cursor to the lower right corner of the cell when it turns to "+". Just double click. It will populate all the cells to the last row.

A V Veerkar

Leal72,

Great! I'm glad it worked for you.