3

I have a data set in excel with 3084 rows. I need to delete 4 rows, then keep one: deleting Tuesday, Wednesday, Thursday, Friday but keeping Monday to create a weekly rather than daily data set.

  • Note: The data is coinciding with a date not a day of the week so I cant simply filter out Tuesdays and so forth [the date appears as 14-Jun-00]).

Is there any way to do this without having to manually go through and delete four rows at a time?

Raystafarian
  • 21,963
  • 12
  • 64
  • 91
Aaron
  • 31

2 Answers2

8

Choose a nearby column and write =MOD(ROW(A1),5) on the first row. This will fill all rows in that column with numbers 0-4. Then simply autofilter out rows you want to delete:
Data > Filter > AutoFilter

Edit: Since it's a recurring date you could probably also just use a wildcard when filtering, ie 14-*

micke
  • 3,485
  • 19
  • 29
0

You can also try a VBA solution - may need to make minor modifications based on your problem but this gives the general idea. This subroutine assumes that you the first cell in the first row is activated, and then the subroutine is run.

Sub sbDelete4Keep1()

    Dim rngProcess As Range
    Dim lCntr As Long
    Set rngProcess = ActiveCell
    lCntr = 1
    While Not IsEmpty(rngProcess.Offset(1, 0))
        Range(rngProcess, rngProcess.Offset(3, 0)).EntireRow.Delete
        Set rngProcess = ActiveCell.Offset(lCntr, 0)
        lCntr = lCntr + 1
    Wend

End Sub
Mozan Sykol
  • 185