Delete 6th 7th consecutive rows in a large Microsoft excel data sheet

Question

I have made a large excel datasheet in MS excel and my mistake added included some repeated data in it, the data in my excel sheet at every 6th 7th position is not needed and I want to del those rows.

I have search all google site plus, stackoverflow to, ave tried importing the data in mysqlworkbench and apply operations but nothing worked.

I just want to remove every 6th and 7th row from my 3000 plus line excel sheet I have attach a snip for clearness the yellow color is only done by me to show that these rows need to be deleted. Any help will be appreciated

how did you generated the excel worksheet? are you able to fix the problem before the generation? — Terry W, Oct 31 '19 at 11:51
@FoxfireAndBurnsAndBurns I have tried like I told in my question that, I tried Importing this table in mysqlworkbench to apply some queries to fix it but i fail, then i tried different method here, like filter rows by data nothing work so I came here — Zero001, Oct 31 '19 at 11:57
@TerryW no sir I cant, this excel sheet is 9 year stock exchange data which I in started without knowing that sat sun market close, had wrote in 3 4 days, now tomorrow day submitting i cant do 3 4 day more i have to do by night. — Zero001, Oct 31 '19 at 12:00
what version of Excel are you using? have you ever used power query as suggested by @RonRosenfeld? It can quickly remove the unwanted rows but it will generate a new table (instead of overwriting the original one) will that be an option for you? — Terry W, Oct 31 '19 at 12:05

Ron Rosenfeld · Answer 1 · 2019-10-31T12:36:30.690

1

Create a helper column with the formula:

=mod(row(),7)

filter for values >1
Select the columns and GoTo Special Visible Cells Only
Copy and paste to new location.

Or you could use Power Query to implement the same algorithm if this is going to be repetitive.

In PQ - Add an Index column starting from zero - Add a custom column with formula: Number.Mod([Index],7) - Filter the custom column by deselecting 5 and 6 - Delete the index and custom columns

All can be done from the GUI.

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index
],7)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> 5 and [Custom] <> 6)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom"})
in
    #"Removed Columns"

EDIT

If what you really want to do is remove Sat and Sun, then the helper column formula should be:

=text(a2,"ddd")

and filter by de-selecting Sat and Sun

edited Oct 31 '19 at 12:36

answered Oct 31 '19 at 11:45

Ron Rosenfeld

53,870
7
28
60

Hi Ron, OP mentioned that the data he actually wants to remove is Saturday and Sunday, so a helper column to convert the dates into Name of Day and use filter function will do the job :) – Terry W Oct 31 '19 at 12:16
@TerryW I initially thought that might be the case, but NONE of the dates that he has shaded in yellow correspond to weekends. If that is the case, he really needs to clarify things, in his question with an edit. – Ron Rosenfeld Oct 31 '19 at 12:23
@TerryW As a matter of fact, NONE of the dates in his example are Sat or Sun – Ron Rosenfeld Oct 31 '19 at 12:25
yes @TerryW, i forget to mention that I tried adding a column of Days, then ran a query that delete the rows there days= sat or sun, but the problem was, when I wrote days, I had to consider that every year has different day on different dates – Zero001 Oct 31 '19 at 12:30
Excuse me Ron, by GUI what do you mean ? – Zero001 Oct 31 '19 at 12:31
@Zero001 `Graphic User Interface`. The menu that pops up when you use `PQ` as differentiated from actually having to write the `M-Code` – Ron Rosenfeld Oct 31 '19 at 12:33
@Zero001 To write a formula in Excel for weekday: `=TEXT(A2,"ddd")` See my edit – Ron Rosenfeld Oct 31 '19 at 12:34
thanks for pointing that out Ron, I guess if the OP is 100% certain that all he wants to remove is every 6th and 7th row from the data, using MOD is safer. – Terry W Oct 31 '19 at 12:35

score 0 · Answer 2 · answered Oct 31 '19 at 11:44

For a fast vba solution, I remembered this question to speed up a sub that hides a large number of rows.

This is easily adapted to hide every 6th and 7th row with a simple counter. If you then swap the .hidden for a .delete you have an extremely quick function giving you the needed result:

Sub murge()
Dim mergedRng As Range
Dim lastr As Integer
Dim cel As Range
Dim i As Integer

lastr = Range("A" & Rows.Count).End(xlUp).Row
For Each cel In Range("A1:A" & lastr)
    If i > 5 Then
        If i > 6 Then
            i = 0
            Set mergedRng = Application.Union(mergedRng, cel)
            Else
                If Not mergedRng Is Nothing Then
                    Set mergedRng = Application.Union(mergedRng, cel)
                        Else
                        Set mergedRng = cel
                End If
        End If
    End If
    i = i + 1
Next cel
If Not mergedRng Is Nothing Then mergedRng.EntireRow.Delete
Set mergedRng = Nothing
End Sub

Please test with some mock data first before deleting valuable data. Depending on whether you have column headers and how long they are, you might need to change the i = 0 at the beginning to be higher or lower.

Delete 6th 7th consecutive rows in a large Microsoft excel data sheet

2 Answers2