Deleting Rows

Here's how. You can record a macro if you wish.

- Insert a column before column A
- Entr in A1 the formula =IF(MOD(ROW(),2)=0,,"") and fill down as far as required
- Select column A and go to Edit>GoTo>Special>Formula>Numbers if you want to delete even rows or Edit>GoTo>Special>Formula>Text if you want to delete odd rows
- Go to Edit>Delete>EntireRow
- Delete column A

Correction .......

Correction :-

The formula should be =IF(MOD(ROW(),2)=0,0,"")

Re: Correction .......

thanks

It says my selection is too large, when i use go to formulas numbers

My limitation using that technique is 16,250 rows- maybe VB is the way to go?

Using the same concept but a different formula
=LOG(ISODD(ROW()))
and selecting either number or error, I get the same error as you are reporting if I select more than 16,250 rows for the "GoTo" special.

An admittedly cumbersome alternative (more like a workaround) that is not limited would be to use yet another column that just had the row number in it (Col C for this example), you could use
=row()
and copy down as far as needed, copy the c column and paste as values.

Then you could sort by the A column, delete all of the rows while they are blocked together, and then resort by col C to get your original order back.

I'm thinking there is a vb way around this. Both Russel Hauf and Faster have posted VB scripts that add every other row, maybe they could reverse them for you? : Can you delete every other row in Excel with a formula or macro. It says my selection is too large, when i use go to formulas numbers

Here are 2 macros ......

It says my selection is too large, when i use go to formulas numbers

Sub Delete_Odd_Rows()
Dim rng As Range, R As Long
Application.ScreenUpdating = False
Columns("A:A").Insert
Set rng = Range(Range("B1"), Range("B65536").End(xlUp)).Offset(0, -1)
With rng
.FormulaR1C1 = "=IF(MOD(ROW(),2)=0,2,1)"
.Copy
.PasteSpecial Paste:=xlValues
End With
Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
R = rng.Find("2").Row - 1
Range("A1:A" & R).EntireRow.Delete
Columns("A:A").Delete
Application.ScreenUpdating = True
End Sub

Sub Delete_Even_Rows()
Dim rng As Range, R As Long
Application.ScreenUpdating = False
Columns("A:A").Insert
Set rng = Range(Range("B1"), Range("B65536").End(xlUp)).Offset(0, -1)
With rng
.FormulaR1C1 = "=IF(MOD(ROW(),2)=0,2,1)"
.Copy
.PasteSpecial Paste:=xlValues
End With
Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo
R = rng.Find("1").Row - 1
Range("A1:A" & R).EntireRow.Delete
Columns("A:A").Delete
Application.ScreenUpdating = True
End Sub

Note ......

Note :-
In the line that sorts [Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo], change the column refs so that all of your data is included.

Re: Note ......

This does not work either.
Compiler error!

Re: Note ......

Works for me.

What line is causing the error and exactly what is the error message ?