Excel selection macro

Assuming that row 1 contains headings and that you want to delete rows based on data contained in column A (from A2 to the last cell in column A with data), then if you want to delete starting with row 2 :-

Sub Delete_Even_Rows()
Dim rng As Range
Set rng = Range(Range("A2"), Range("A65536").End(xlUp))
rng.EntireColumn.Insert
With rng.Offset(0, -1)
.FormulaR1C1 = "=IF(MOD(ROW(),2)=0,1,"""")"
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
.EntireColumn.Delete
End With
End Sub

If you want to delete starting with row 3 :-

Sub Delete_Odd_Rows()
Dim rng As Range
Set rng = Range(Range("A2"), Range("A65536").End(xlUp))
rng.EntireColumn.Insert
With rng.Offset(0, -1)
.FormulaR1C1 = "=IF(MOD(ROW(),2)=1,1,"""")"
.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
.EntireColumn.Delete
End With
End Sub

A few comments .......

:

I notice a lot of requests on this board for macros that can be created by using the macro recorder (which, of course, requires no knowledge of VBA).

Such requests could be avoided if the posters were first to perform the tasks manually with the macro recorder turned on and then check to see whether the resulting macro does what is required.

This request to delete every other row is a good example of being able to create a macro (with the recorder) without any VBA knowledge :

- turn on the macro recorder
- select a range of cells within a column covering the rows for deletion
- Insert>Columns
- type =IF(MOD(ROW(),2)=0,1,"")
- press Ctrl+Enter
- Edit>GoTo>Special>Formulas>Numbers
- Edit>Delete>EntireRow
- Edit>Delete>EntireColumn
- turn off the macro recorder

The recorder should have produced this macro :-

Sub Macro1()
Range("A1:A10").Select
Selection.EntireColumn.Insert
Selection.FormulaR1C1 = "=IF(MOD(ROW(),2)=0,1,"""")"
Selection.SpecialCells(xlCellTypeFormulas, 1).Select
Selection.EntireRow.Delete
Selection.EntireColumn.Delete
End Sub

Delete the first line of the macro and it can then be used on any selection of contiguous cells in a column

Re: A few comments .......

Thanks Billy,
I wish I could have created this macro. I actually tried to do it but was not able to. I read through your statements on both responses and still didn't understand what some of the statements were...and some i did understand, i would have never tried on my own. I kind of know how you feel in that I should be able to do some type of macros. If it involves any "IF" inputing or anything remotely related to VB or low level programming...I am lost. I wish I had time to learn more about excel but I need to support excel and 20 other applications with my co-workers. I am going to try to break apart your macro and understand it though. If you had any good resources for learning more than just a basic macro, please let me know. Thanks again,
Jeff Little

Re: A few comments .......

I did not intend any criticism, was just trying to provide some comments intended to be helpful.

The main point I was trying to make is that before trying to create a macro, work out the best way of doing it manually. More often than not, the macro recorder can then be used to create a macro and often without having to "tweak" the macro afterwards. In such cases, no VBA knowledge is required.

Also, if you are developing a macro and you don't know(or can't remember) the code for a particular action, use the recorder to find out the code. Even very experienced VBA programmers frequently do this.

There is a good example in a posting today where the poster has a macro which pastes and copies a range of cells. After the macro has finished, the copied cells are still highlighted with a moving border and he has to press the Esc key to remove the highlight. His question was what code could be added to his macro so that the macro removes the highlight.
To find out the code, all he need do is copy and paste a range of cells, turn on the recorder, press the Esc key, turn off the recorder, and he then has his code.
Thanks Billy,