Excel 2007 : A macro to insert an empty row based on specified criteria

[TBJV]

Hi folks,

I am a noob when in comes to macros but I would be grateful for a solution that piggie backs on the macro written below by Leith Ross. I'm looking to write a macro to insert one entire row into a set of data after each data group ends. The macro below only inserts a cell between each data group.

E.g. Column A contains the following data starting with A1, then A2 etc. I also have data in columns B, C, D, E etc... The empty row would move all data in the row down, not just the column A. However, the criteria for the data group will still be column A. Does this make sense?

A1 = BB
A2 = BB
A3 = BB
A4 = CC
A5 = CC
A6 = CC
A7 = DD
A8 = DD
A9 = EE
A10 = EE

I would like to run a macro which will insert one entire row after each similar data sets ends.

The result would be the following

A1 = BB
A2 = BB
A3 = BB
A4 = (EMPTY ROW)
A5 = CC
A6 = CC
A7 = CC
A8 = (EMPTY ROW)
A9 = DD
A10 = DD
A11 = (EMPTY ROW)
A12 = EE
A13 = EE


[INDENT]'Written: Sept. 18, 2007
'Author: Leith Ross
'Summary: Add blank cells where the data changes

Please Login or Register  to view this content.

Thanks in advance.

Tom

[xladept]

Just change this snippet:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

[TBJV]

Fantastic.... exactly what I wanted.. thanks a bunch.

[xladept]

You're welcome!

[slk1186]

Whats posted in here is really useful, and gets me so close to what I need!

Ultimately I need to do exactly what is above, but my data is structured a little differently so it would look like this

A1 = AA
A2 = Blank
A3 = Blank
A4 = BB
A5 = Blank
A6 = CC
A7 = Blank
A8 = Blank
A9 = Blank
A10 = DD

etc.

What I would want to do is insert a single blank line above each populated cell. So above the AA, BB, CC and DD rows. The code above actually inserts two lines where i just want one since it detects a new set data set each time it switches from a populated to blank cell and then again when it goes from blank to populated. Any help would be greatly appreciated and save me hours of monkey work

[xladept]

Try:

With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) <> Cells(R - 1, Col) And Cells(R,Col) <> "" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With

[slk1186]

Ah! works perfectly. Thanks so much!

[xladept]

You're welcome!