Macro to insert rows part II :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Macro to insert rows part II
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Jillbeirne
Board Regular


Joined: 17 Sep 2003
Posts: 7

Flag: Blank

Status: Offline

 Reply with quote  

Macro to insert rows part II

A few days ago I posted this question:

i needed a macro that would go through the cells in column A, which is a list of adverstiser and insert a row when the name of the advertiser changed. So the macro needs to compare each cell to the one before and when the values differ, insert a macro. But I need one that will first go through column A, and then once finished with the items in A, go through column B and do the same thing. Can't get the column B to work... Thanks so much for any help!

-J

Post Fri Sep 19, 2003 6:58 pm 
 View user's profile Send private message AIM Address

Mat
Board Master


Joined: 17 Sep 2003
Posts: 451
Location: Montréal, Québec
Flag: Canada

Status: Offline

 Reply with quote  

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
_________________


Mat


"I will not compromise my ideals, music or nap schedule" - Trent Lane

Post Fri Sep 19, 2003 7:13 pm 
 View user's profile Send private message

Iridium
Board Master
Board Master


Joined: 16 Jul 2002
Posts: 2581
Location: Walsall, England
Flag: England

Status: Offline

 Reply with quote  

Re: Macro to insert rows part II

Would help if you replied to/linked the other thread
_________________

Post Fri Sep 19, 2003 8:05 pm 
 View user's profile Send private message

Jillbeirne
Board Regular


Joined: 17 Sep 2003
Posts: 7

Flag: Blank

Status: Offline

 Reply with quote  

Re: Macro to insert rows part II

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

Post Fri Sep 19, 2003 8:13 pm 
 View user's profile Send private message AIM Address

Iridium
Board Master
Board Master


Joined: 16 Jul 2002
Posts: 2581
Location: Walsall, England
Flag: England

Status: Offline

 Reply with quote  

Re: Macro to insert rows part II

quote:
Originally posted by Jillbeirne:

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

-J


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 icon_wink.gif
_________________

Post Fri Sep 19, 2003 8:20 pm 
 View user's profile Send private message

Von Pookie
MrExcel MVP


Joined: 18 Feb 2002
Posts: 3078
Location: The act or process of locating.
Flag: Usa

Status: Offline

 Reply with quote  

Just a little "housekeeping" on my part, here icon_smile.gif

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


Post Fri Sep 19, 2003 8:25 pm 
 View user's profile Send private message

GaryB
Board Master


Joined: 26 Feb 2002
Posts: 156
Location: Essex, England
Flag: Uk

Status: Offline

 Reply with quote  

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

Post Fri Sep 19, 2003 10:51 pm 
 View user's profile Send private message

Jillbeirne
Board Regular


Joined: 17 Sep 2003
Posts: 7

Flag: Blank

Status: Offline

 Reply with quote  

Re: Macro to insert rows part II

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

Post Sun Sep 21, 2003 2:21 pm 
 View user's profile Send private message AIM Address

GaryB
Board Master


Joined: 26 Feb 2002
Posts: 156
Location: Essex, England
Flag: Uk

Status: Offline

 Reply with quote  

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

Post Sun Sep 21, 2003 5:37 pm 
 View user's profile Send private message

CartoonGal
Board Regular


Joined: 21 Sep 2003
Posts: 9

Flag: Blank

Status: Offline

 Reply with quote  

Re: Macro to insert rows part II

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

Post Sun Sep 21, 2003 9:13 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.