MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel selection macro


Posted by jeff little on September 25, 2001 11:16 AM

Hi,
I am having to select and delete every other row on multiple excel 2000 workbooks. Is there any way to create a macro to go in and do the deleting of every other row?
Thanks, Jeff l.


Posted by Billy on September 25, 2001 3:58 PM


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

Posted by Billy on September 25, 2001 4:32 PM

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

Posted by Jeff Little on September 26, 2001 4:42 AM

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

Posted by Billy on September 26, 2001 3:39 PM

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,