Auto delete row in this specific pattern

**truongn2** · 08-03-2007, 05:10 PM

Hi,

Can someone help me make a macro that would go through and delete rows in this pattern below:

1. Keep
2. Delete
3. Delete
4. Delete
5. Delete

6. Keep
7. Delete
8. Delete
9. Delete
10. Delete

Also, how would I trigger the macro?

Thanks,
truongn2

**boylejob** · 08-03-2007, 11:01 PM

truongn2,

How about something like this. Of course you will need to make a few modifications so it will fit your workwook.

This codes starts at the bottom and works it way to the top. It deletes 4 rows and then skips one.

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 = 1

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

Next lRow
End Sub

**FrankBoston** · 08-03-2007, 11:20 PM

You might find a non-macro approach useful for similar problems.

Add new columns say A and B for data rows 1 to 100 (or whatever rows)
Put 1 in A1 and use Menu Edit: Fill: Series to place numbers 1 to 100 in A1 to A100.
Use a formula in B to calculate 0 for Delete or 1 for Keep, for each row. Fill Down to generate values in all rows of your data, or just paste the pattern of 0's and 1's that you need.
Sort the data rows into order on B. All the rows for deletion are now next to each other. Select them and Delete Rows.
Sort the remaining data on A to get back your original order. You can delete A and B.

I know this is the Programming Forum, but it can be tiring to write macros all of the time for unusual situations.

Thread: Auto delete row in this specific pattern

Thread Tools

Display

Auto delete row in this specific pattern

Find and elimnate rows without VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions