Determine selected cell to start macro 1

[mllex]

The following macro inserts a blank row after each row of data. I need to be able to run this macro from any point in an Excel worksheet. How do I get it to recognize the selected cell as the starting point?

Dim x As Integer
x = 2
Do While Cells(x, 1) <> &quot;&quot;
Cells(x, 1).EntireRow.Insert
x = x + 2
Loop


TIA

 

[SkipVought]

Diva,

ActiveCell is the range object for the selected cell in the selection range (since you can have more than ONE cell selected).

But let me ask a stupid question: Why are you inserting empty rows between data? I hope this is not a sheet that you use for data analysis. Tell me that this sheet is ONLY used for display purposes. Otherwise, you create ALOT of data analysis problems down the road.

Hope this has helped Skip,
metzgsk@voughtaircraft.com

 

[mllex]

This is for display purposes only, and a macro I am creating for another user.

The spreadsheets usually have a heading, and then a list of data that has been extracted from another source, so I need to be able to select a cell, and then have rows inserted between rows of data below that. My macro only works if the entire spreadsheet is data. I haven't been able to get the activecell piece incorporated. Apologies...I don't work with this every day, but it certainly intrigues me and I like to use it where I can.

Many thanks.

 

[RobBroekhuis]

If you're only looking for more spacing between rows, you'd do better to simply increase the row height. I think the following might work:

activesheet.range(activecell,activecell.End(xlDown)).rowheight=30

Rob

 

[SkipVought]

Diva,

Probably what you want to do then is...

    Dim x As Long
    x = ActiveCell.Row + 1
    Do While Cells(x, 1) <> &quot;&quot;
        Cells(x, 1).EntireRow.Insert
        x = x + 2
    Loop

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[larryww]

I agree strongly with Rob if that is suitable. You get the appearance of a blank line without the discontinuity that robs you of end-down, etc.

If you go that way, I wrote a routine to propagate row heights, though (brace yourself) Skip wasn't impressed [LOL]. The thread was &quot;Paste Special/Row height - code for the taking&quot;, thread707-227411

 

[mllex]

Thanks, Skip.

That's exactly what I wanted.

The row height idea was explored, but since the user is preparing these materials for the Board of Directors, and sometimes she must put shading in the blank rows for cosmetic reasons, the row height option does not work for her.

Many thanks for your input, Larry.