Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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-*

share|improve this answer
1  
Perhaps a variation - try WEEKDAY function, e.g. =WEEKDAY(A1) in adjacent column, then filter by 2 in that column (monday = 2) –  barry houdini Mar 1 '12 at 23:09
    
worked like a charm, thanks –  Aaron Mar 2 '12 at 0:11
1  
Aaron, be sure to uptick this answer then to make it the "Accepted" answer. –  Jerry Beaucaire Mar 2 '12 at 1:25

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.