Need macro that performs same action top to bottom.

[Guest]

How can I write a macro that performs the same action on every row from top
to bottom once I start it.

 

[tghcogo]

More info please,

unless of course you want to perform the action on all 65536 rows!

TGHCOG

 

[Guest]

I have a sheet with 810 rows and I need to insert a blank row between each.
I know how to write a macro that will insert a blank row, but it only works
one at a time. I'd like to start at the top and have it insert a blank
between the first two and then drop down and repeat itself over and over
untiul it gets to the bottom of the sheet. That I do not know how to do
since I am a novice at macros. Would appreciate your help.
George

 

[Myrna Larson]

For this purpose, you definitely want to start at the bottom and work up. That
said, are you SURE this is what you want? If it's just for visual effect (i.e.
spacing), you will have fewer problems with formulas if you just increase the
row height. That said, this macro will insert a new row between each of the
existing rows. I have assumed that your worksheet has 65,536 rows, and the
last used row can be identified by looking at column A.

Sub InsertRows()
Dim r as Long
Application.ScreenUpdating = false
r = Range("A65536").End(xlUp).Row
For r = r To 2 Step - 1
Cells(r, 1).EntireRow.Insert
Next r
Application.ScreenUpdating = True
End Sub

The reason you start from the bottom is that the instant you insert a new row,
the rows below it are renumbered, which makes a royal mess of the For/Next
loop.

 

[Guest]

Thanks for the help. Myrna, I'll give it a try.
George

 

[Guest]

If the cells in column A may be blank when other cells in the row aren't or
if A65535 may not be blank you may want to use r =
cell.specialcells(xlcelltypelastcell).row in place of r =
Range("A65536").End(xlUp).Row

Thanks for the help. Myrna, I'll give it a try.
George