Delete every 2nd and 3rd row

[richiwatts]

I have an excel column with about 5000 rows of text. Is there a quick way to delete every 2nd and 3rd row? There is nothing in the text that would allow me to filterin a smart way.

Currently
1
2
3
4
5
6
etc.

I need to delete rows 2,3 5, 6 and so on.

Thanks

 

[Ron Coderre]

typically, you'd add a helper column with a formula like:
=MOD(ROW(),2)
or
=MOD(ROW(),3)
...then filter on that column for the values in rows to remove.

Is that something you can work with?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG08May17
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range, Lp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] Lp = 1 To 2
    Rw = IIf(Lp = 1, 3, 2)
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dn.Row Mod Rw = 0 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
    nRng.EntireRow.Delete
[COLOR="Navy"]Set[/COLOR] nRng = Nothing
[COLOR="Navy"]Next[/COLOR] Lp
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[richiwatts]

Thanks Ron and Mick they both worked a treat.

 

[MickG]

You're welcome