Macro to delete 3 rows, skip row, delete 3

[Etb]

Newbie ques. Is it possible to write a macro for this?

Alternative would be to delete all rows except every 4th row throughout sheet.

 

[shrivallabha]

Welcome to Chandoo Org Forums!

Assuming that you have data in Column A and Column B is empty then below macro will identify all rows not to be deleted as you have mentioned.

Public Sub IdentifyDeletionRows()
Dim i As Long
For i = 4 To Range("A" & Rows.Count).End(xlUp).Row Step 4
    Range("B" & i).Value = "Do not delete"
Next i
End Sub

Study the code and then come back with doubts.

Edit: I misunderstood the OP requirement, now the code and response is adjusted.

 

[AlanSidman]

If you have power Query available then here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Modulo" = Table.AddColumn(Source, "Modulo", each Number.Mod([Column1], 4), type number),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Modulo", each ([Modulo] = 3)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Modulo"})
in
    #"Removed Columns"

File example attached. Also, look at the link in my signature for further information.

 

[Etb]

Thanks to Alan and Shriv for the responses.

I have a 2007 version of excel, so doubt I have query.

Any chance you could set this to run compatible for 2007 and save it in a workbook which I can download and into which I can paste my data, all of which is in column 1. Tks

 

[AlanSidman]

Power Query is only available on Windows Machines running Excel 2010 or later.

 

[Etb]

Ok, can it be done without query

 

[shrivallabha]

Ok, can it be done without query

Etb, see my post which is a VBA solution. You can also use a formula and auto-filter to achieve this.

To insert VBA code in your workbook you can refer instructions in below link
https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

 

[AlanSidman]

Here is another means.

Run this VBA, then in column K in my example, in the file attached above, filter out 1,2 and 3. Once you have tested in my example, adjust code to your situation

Option Explicit

Sub InsertFour()
    Dim lr As Long, x As Integer
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    x = 0
    For i = 2 To lr
        If x = 4 Then x = 0
        Range("K" & i) = x + 1
        x = x + 1
    Next i
End Sub

 

[Etb]

Thanks Alan and Shriv earlier.

It seems you have the code written which apparently requires no query.

I hope it will repeat over and over deleting 3 rows and retaining every 4th row.

I apologize for being a newbie.

Could you kindly include it in an attached workbook, saved back to excel 2007?

Many Thanks, etb

 

[salim hasan]

Try this macro

Option Explicit
Sub del_rows()
    Dim i%
    Dim MY_RG As Range
For i = 4 To Cells(Rows.Count, 1).End(3).Row
    If i Mod 4 <> 0 Then
       If MY_RG Is Nothing Then
           Set MY_RG = Range("a" & i)
        Else
           Set MY_RG = Union(MY_RG, Range("a" & i))
       End If
    End If
Next

MY_RG.EntireRow.Delete
End Sub

 

[Etb]

progress!

I created a sheet entering sequentially numbers 1-12 in column a

The macro removed data on rows 5,6,7 and 9,10,11. Close but not exactly right, but that's ok because the file I'm trying to edit has changed.

Can this be adjusted to remove all rows except row 7,13,19 etc (removing 6 rows at the beginning and in between)

 

[AlanSidman]

crossposted: https://www.ozgrid.com/forum/index....-4th-row-in-excel/&postID=1226337#post1226337

 

[AlanSidman]

Click on Alt + F8 and run the macro

 

[vletm]

Etb
You should reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Etb]

Salims macro worked earlier except the rows I need to delete have changed to wanting to retain every 6th row now.

On Alans book1 above In excel 2007, I get run time error 1004
"Delete method of range class failed"

Any chance someone could test it on the small file test a I attached ?

 

[salim hasan]

change the two lines in my code
For i = 4 To Cells(Rows.Count, 1).End(3).Row
If i Mod 4 <> 0 Then
To
For i = 1 To Cells(Rows.Count, 1).End(3).Row
If i Mod 6 <> 0 Then

 

[AlanSidman]

Your sample file is not representative of what you described in Post #1. How can we provide you with a valid solution if you do not give us correct data to start with. I am finished with this -- good luck with finding a solution.

 

[Etb]

Thanks to both Alan and Salim

Salims code works perfectly on a small file. except that performing this om 24000 records may take forever, Been running 10 min so far.

Should i just wait or did i read somewhere that if you insert a few line of code and it runs 50x

 

[Etb]

It mostly worked! took about 15 min and that's fine.

Only problem is I began with 24,000 records and Salims macro reduced it to 1269.

Since I was trying to remove 5 lines of every 6, it seems it should have left me with about 4000 records.

Not sure what to try now. You guys have been a terrific help!!

 

[Etb]

Salim suggested I alter the macro as follows, adding the Dim i As Long instead Dim i%, as shown below.....

Ran it, and still got only 1/3 of the desired result. Should have 4,000 records of orig 24,000, but still just getting 1200!

>>> use code - tags <<<
​

Sub del_rows()
    Dim i As Long
    Dim MY_RG As Range
For i = 6 To Cells(Rows.Count, 1).End(3).Row
    If i Mod 6 <> 0 Then
       If MY_RG Is Nothing Then
           Set MY_RG = Range("a" & i)
        Else
           Set MY_RG = Union(MY_RG, Range("a" & i))
       End If
    End If
Next

MY_RG.EntireRow.Delete
End Sub

 

[Marc L]

As per forum rules if at least you attach a source sample workbook and accordingly the expected result workbook …​

​

 

[Etb]

Ok small sheet attached. Not sure if macro is included, but the macro code is a few posts above.

Keep in mind, Ive been able to successfully delet every 6th row in small workbooks like this, however when I run the macro above against 24,000 rows, it yields just 1200 rows remaining instead of the target number of about 4,000
I can send the larger file, but I gather large file are typically not wanted here for testing.

 

[Marc L]

Well read this time my previous post then explain what should we understand about this attachment !​

Now it's every 6th row but in your initial post it's 4th !​

So be smart to just find another criteria than the row # …​

​

If I well catch your need it can be achieved easily and fast without any code just following a child way​

and of course easy to reproduce under VBA without any loop !​

​

And for those who do not well know Excel (or do no respect the main rule when writing a VBA code),​

according to the post #15 attachment, there are another looping ways than deleting any row,​

needing less than ten codelines, one only five …​

​

 

[Etb]

The code I posted nearly works except it does not give full results. Cuts off at 1200 results.

I just want to carve out the emails in the list.

Not sure if better code.

What is child's way?

 

[Marc L]

Child way is when any can operate manually, easily just with Excel Basics and faster than some bad looping codes​

and 'cause I yet saw children achieving this kind of 'deleting' process with success !​

​

BUT, AGAIN, as it may depend on your final expected result layout and as I'm still expecting it​

since my post #21, just read it again and well this time ! …​

​

As your post #22 attachment is so 'poor', in case your post #15 attachment is closer to the real one​

let us know when attaching the one we expect in fact since your initial post as per forum rules.​

4/5 ways from post #15 but as some can't work with post #22 so accurate attachment is necessary …​

​