Macro to insert rows part II

Not tried, but I think the code would be the same, except that you compare cell in column B instead of column A... Well, sure it depends of what you want to do with the blank cells added in column B when you first inserted rows when you go trough the column A.

I don't know how you could be able to do it with column A and not with column B...

What is your macro?

Mat

Would help if you replied to/linked the other thread

Sub InsertRows()
Dim r As Long
Dim adv As String
Dim i As Long

r = Cells(Rows.Count, "A").End(xlUp).Row

adv = Cells(r, 1).Value

For i = r To 2 Step -1
If Cells(i, 1).Value = "" Then
adv = Cells(i + 1, 1).Value
Else

If Cells(i, 1).Value <> adv Then
adv = Cells(i, 1).Value
Rows(i + 1).Insert
Rows(i + 1).Insert
End If
End If
Next i

End Sub


This is the one im using now: but substituting B for A doesn't work for some reason: when i run it, nothing happens.

sorry for not linking this to my last post; figured no one would see it since it was a few days ago!
tHaNkss!!

-J

Jillbeirne said:

sorry for not linking this to my last post; figured no one would see it since it was a few days ago!
tHaNkss!!

-J

Click to expand...

Just for info if you reply to a post (no matter how old it is) it'll jump to the top of the board - I'll have a look at your problem

Just a little "housekeeping" on my part, here

Original post: http://www.mrexcel.com/board2/viewtopic.php?t=63507 (is now locked with direction to this thread)

The previous macro will insert 2 rows where the change of advertisers name happens in column A. Because entire rows are inserted it will also put breaks in B.
If you did exactly the same on column B afterwards it will put extra breaks in A as well and it starts to look a bit disjointed.

harry bert
harry bert


steve bert


steve fred
steve fred

Is this want you want or were you after something like this?
harry bert
harry bert
. . . . .bert (ignore the ...'s. It wouldn't line up without them!)

steve
steve fred
steve fred

If it's the latter then I think this will get you there

Sub InsertRows()
Dim r As Long
Dim adv As String
Dim i As Long
Dim Col As Integer

For Col = 1 To 2

r = Cells(65536, Col).End(xlUp).Row

adv = Cells(r, Col).Value

For i = r To 2 Step -1
If Cells(i, Col).Value = "" Then
adv = Cells(i + 1, Col).Value
Else

If Cells(i, Col).Value <> adv Then
adv = Cells(i, Col).Value
Cells(i + 1, Col).Insert Shift:=xlDown
Cells(i + 1, Col).Insert Shift:=xlDown

End If
End If
Next i
Next Col
End Sub

Of course, if I've got all wrong, could you show what you would expect to see?

HTH
GaryB

Thanks so much for your help; the only problem is that the macro inserts a cell after each change in advertiser for both column A and B instead of inserting an entire row: the original should look like this:

a a
a a
a b
b c
b d
c e
c e
c f

...and end like this:

a a
a a

a b

b c

b d

c e
c e

c f

does that make sense: first insert a row after every change in A, then go back and insert a row after every change in B! This website is the best and I appreciate all the help !!

-J

It makes perfect sense thanks. Here's an edited version that I think finally does what you asked in the first place!

Sub InsertRows2()
Dim r As Long
Dim adv As String
Dim i As Long
Dim Col As Integer

For Col = 1 To 2

r = Cells(65536, Col).End(xlUp).Row

adv = Cells(r, Col).Value

For i = r To 2 Step -1
If Cells(i, Col).Value = "" Then
i = i - 1 '**
adv = Cells(i, Col) '**
Else
If Cells(i, Col).Value <> adv Then
adv = Cells(i, Col).Value
Rows(i + 1).Insert
End If
End If
Next i
Next Col
End Sub

I've assumed (very dangerous on my past track record!) that you would only want one line inserted between the B D and C E, but if you wanted two (as they are both changing) just take out the two lines marked with '**. Also it assumes that there is a title row. If you have no headers then change the line
For i = r To 2 Step -1 to For i = r To 1 Step -1

Hope this (finally!) helps

GaryB

GARYB whoever you are, you are a genius!!

Thanks you so much! my co-workers will be very pleased =) And this cuts down my time considerably!

MILLIONS OF THANKS!!

-J