macro to insert rows between sets of records

I have data that looks like this:

name  class  title
joe    class1   assistant
mary   class1   assistant
zelda  class1   assistant
al   class1   professor
ben   class1   professor
cleo   class1   professor
dan   class1   professor
alice   class2   student
barb   class2   student
claire   class2   student

...and so on the spreadsheet goes.  What I want to do is create a macr
that will insert 2 blank lines between each 'title' group and insert 
line above the groupings with the title on it.. so that the resultin
spread sheet looks like:

name  class  title

assistant
joe    class1   assistant
mary   class1   assistant
zelda   class1   assistant


professor
al   class1   professor
ben   class1  professor
cleo   class1   professor
dan   class1   professor


student
alice  class2   student
barb   class2   student
claire   class2   student

Can someone help me with such a macro for Excel?
Thanks
A

--
Message posted from http://www.ExcelForum.com

0
8/2/2004 6:27:40 PM
excel 39879 articles. 2 followers. Follow

1 Replies
530 Views

Similar Articles

[PageSpeed] 39

AA,

Try the sub below.  Change the

myCol = 3

to correspond to your column of titles (column C = 3)

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myRow As Long
Dim myCount As Long
Dim myCol As Integer

myCol = 3
myCount = ActiveSheet.UsedRange.Rows.Count

For myRow = myCount - 1 To 1 Step -1
If Cells(myRow, myCol).Value <> Cells(myRow + 1, myCol).Value Then
Range(Cells(myRow + 1, myCol), Cells(myRow + 2, myCol)).EntireRow.Insert
Cells(myRow + 2, 1).Value = Cells(myRow + 3, myCol).Value
End If
Next myRow
End Sub


"felder >" <<felder.1adj02@excelforum-nospam.com> wrote in message
news:felder.1adj02@excelforum-nospam.com...
> I have data that looks like this:
>
> name  class  title
> joe    class1   assistant
> mary   class1   assistant
> zelda  class1   assistant
> al   class1   professor
> ben   class1   professor
> cleo   class1   professor
> dan   class1   professor
> alice   class2   student
> barb   class2   student
> claire   class2   student
>
> ..and so on the spreadsheet goes.  What I want to do is create a macro
> that will insert 2 blank lines between each 'title' group and insert a
> line above the groupings with the title on it.. so that the resulting
> spread sheet looks like:
>
> name  class  title
>
> assistant
> joe    class1   assistant
> mary   class1   assistant
> zelda   class1   assistant
>
>
> professor
> al   class1   professor
> ben   class1  professor
> cleo   class1   professor
> dan   class1   professor
>
>
> student
> alice  class2   student
> barb   class2   student
> claire   class2   student
>
> Can someone help me with such a macro for Excel?
> Thanks
> AA
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Bernie
8/2/2004 7:08:35 PM
Reply:

Similar Artilces:

Change the formatting of row by select a single cell & Editing should be working
Hi All; I require such a sheet code which can help me in Changing the formatting of row (like A1:A25) of selected cell (A4 is Selected) and allow me to use editing (Cut, Copy, Paste, Undo etc). Currently I'm Using following code but this does not allow me to use editing (Cut, Copy, Paste, Undo etc) and I can not decide the range for working this sheet code. Any kind help is appreciated. Private Sub Search_Click() Sheets("Quick Search").Visible = True ActiveWindow.SelectedSheets.Visible = False Sheets("Quick Search").Select End Sub Private Sub Worksheet_...

How can I set up an inventory control system on Excel?
Can I use Excel 2000 for an inventory control system or do I need to buy additional software? There are about 200 inventory items. They are sold as is, but they are also used as components in a final product. This is for a small job shop that currently keeps inventory with pencil and paper. Thanks for any suggestions. You can download templates here http://office.microsoft.com/en-us/templates/CT061995421033.aspx -- Regards, Peo Sjoblom "webbmarketingsolutions" <webbmarketingsolutions@discussions.microsoft.com> wrote in message news:57422FE7-EF1B-40E4-9CD6-CD83428...

macros in MFC
I have read some source codes in MFC. Most definitions of macros can be read by "go to definition" opration,but still some can not be found.For example ,AFX_NOVTABLE in CObject definition and AFX_COMDAT in BEGIN_MESSAGE_MAP definition. Anyone can tell me how to find their definitions? Thanks a lot! hhw "hhw" <hhw3@163.com.discuss> wrote in message news:OpJ9cUa0DHA.1532@TK2MSFTNGP10.phx.gbl... > I have read some source codes in MFC. Most definitions of macros can be read > by "go to definition" opration,but...

Save a record when switching tabs or clicking button
Hey there, When a user enters data on my form, and then either switches to another open tab, OR clicks on a button on the current form that opens another form, I want their data to be saved. How can I go about this? I tried the OnDirty event of the form as well as the Lost Focus event and neither is firing so I am at a loss. Any help is appreciated! Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 In the button's click event, add If Me.Dirty = True then Me.Dirty = False. On the tab control's change ev...

How to set a discount like: buy 3 for $100.
How to set a discount like: buy 3 for $100. I want to set a discount like: buy 3 itemA for $100. If those will be mixed items you have to first setup a discount under database tab in manager, and than in items properties under discount tab select "USE mix and match Discount scheme" and select the discount you created. If its going to be same item you can use QTY discount pricing table. Hope this helps Robert "TJW" wrote: > How to set a discount like: buy 3 for $100. > I want to set a discount like: buy 3 itemA for $100. I could use that for EVEN QTY, bu...

set field data as control source of another field in a report
In a table I have fields as [a], [b] .... and so on. for each record I have a special calculation, say [a]+[b] for a recored; [a]*[b]*[d] for another recored, and so on. I would like to use these calculation formulas which I have saved in a field of the same record as a calculation (as a data in a seperate field) in a report Have I misunderstood this. When you say that you have a special calculation for each record in your table, do you mean that you have typed the text a + b + c in your table? If yes, may I suggest that you do this in a query grid so that the query shows the result of you...

front end \ back end set up
How do I get my front end to connect to the back end. Yea that's what my wife said. Check the box on the Front-end and you should be all set. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Gonzosez" <tbyam@hinklemfgNS.com> wrote in message news:urHL2vXgEHA.1344@TK2MSFTNGP11.phx.gbl... > How do I get my front end to connect to the back end. > Yea that's what my wife said. > > .... and maybe some flowers or chocolates for your wife ;) -GT "Mark Fugatt [MVP]" <news@4mcts.com> wrote in message ne...

vbscript start excel run macro not working
I am trying to run a vbscript that opens excel then executes a macro. I have code that works but I get an error saying "The macro "sort" can not be found. I found that if you open excel by the script it does not load any of the macros. I did some searching and found that by opening excel via automation does not load all addins and startup files to load quicker. Is there something that I can put in the script that will load the macro files? Here is what I have for the script. Code: -------------------- filePath = "C:\Scripts\capplot\utilization.csv&...

Set IE's favorite data... how?
Hello~ I got all my IE's favorites data by GetPrivateProfileString( "InternetShortcut", "URL", "nothing"...); Hmm.. That's ok. So, that I wonder is how to set favorites(bookmark) data... reverse work in GetPrivate...() If I make the *.url files and calls SetPrivateProfileString("InternetShortcut", "URL", "nothing"...);, is it works? Could you give me some advice? And I don't know how to get Netscape's bookmark. As I know, Netscape reposit bookmarks data in bookmarks.htm. But the folder that htm file is in, looks no...

How do I insert PPT into a web site?
I am trying to insert a PPT into my web site (www.theinitiators.info) and I've tried following several different sets of directions in doing so. When I go to the web page and click on the PPT, it tells me the page cannot be found. I wonder if someone could take a look at my site and see if you can figure out what I'm doing wrong? I would really appreciate any help! Thank you! Susan www.theinitiators.info -- Thanks in advance for your help! Ensure the PPT presentation is at the location that you're linking to (../../Career%20Switchers/Small%20Group%20Project/...

Inserting and wrapping text around pictures
I need to insert a picture and wrap text around it BUT keep the picture LINE and the text fully wrapping. How do I achieve that? Also, I want to do a global format of new pages before downloading a document from MS Works into publisher. How do I do that? JCM <JCM@discussions.microsoft.com> was very recently heard to utter: > I need to insert a picture and wrap text around it BUT keep the > picture LINE and the text fully wrapping. How do I achieve that? There is no way to accomplish this. In-line images mean that the images appear on a line in the same way they do when you se...

Outlook2002- account settings mysteriously change
About 1-2 times a month my email settings mysteriously change themselves. In particular, the "Incoming mail server (POP3)", changes from it's correct setting to localhost. The text from here is then placed at the end of my "User Name". So, every so often it won't download messages from this particular account and I have to go into my email accounts and change it back. Any ideas on how to stop this? Thanks in advance for any assistance. Shawn M. The mystery can be solved by disabling the mail scanning in your AV. --� Milly Staples [MVP - Outlook] Post...

Inserted Row has no formulas in it
When you insert a row to a sheet with formulas (that proceed down a column, e.g. a 'running total' for example), the formula doesn't appear in the inserted row? I know you can copy/paste the formula manually, but since the formula applies to each row, why doesn't the program put it there automatically? I am using 2000, build 9.0.6926 SP-3 Thanks, Lee Bowman Here's a workaround: copy one of the rows that has the formulas. Go to the row above which you want the insert, and select the entire row. Press Ctrl and the + key on the keypad. This inserts a complete copy of ...

Is a simple macro possible for this useful idea (Excel 2000)?
Need a shortcut-key combo that does the following while Editing a character in ANY particular cell: to CHANGE THAT HIGHLIGHTED character or characters to Superscript.Thus, with only a simple shortcut-key, I'd more easily make the 'squared' or 'cubed' characters (a 2 and/or 3), RATHER THAN BE Forced to repeatedly Hunt around the formatting menus repeatedly. Please note, I unsuccessfully tried to create this by using the MacroRecorder, but that created a 'hard-wired' code that only applied to the text in a particular cell and Copied THAT SAME CELL'S content...

Scheduled Macro hanging
Hi: I have an Access 2003 macro running on a Windows 2003 server box. I am logged into the box as Administrator. The macro runs fine when I run the shortcut to the macro manually by clicking on it. However, when I schedule it, it shows in the task scheduler window as "running" and doesn't finish. I scheduled the task to run using the same Administrator user. Also, I ensured no pop up would come by disabling the Tools Options Edit/Find prompts, and set the macro security to "low". As I said, when I run it manually it runs fine with no prompts. The macro only queries a ...

Outlook 2000, Where's "Insert Item" when using Word as Editor?
I can't "Insert Item" when sending/replying HTML formated email when using Word as editor. The "Insert Item" button does not show up in the Insert menu. Is it a limitation or I have to make some setting? Click the dropdown next to the Paperclip on the Toolbar. -- Russ Valentine [MVP-Outlook] "Jason Chan" <no@mail.com> wrote in message news:OSDQWl4IFHA.2884@tk2msftngp13.phx.gbl... >I can't "Insert Item" when sending/replying HTML formated email when using > Word as editor. > > The "Insert Item" button does not ...

How do I set up a word document application for online use
In our basket offcials asociation, I want to have an application in word I can send to members who can down load my word document and type in answeres in a radio box?? or Text box???? save and sent back to me. I could do it in the old version but not 97 -- rst See http://word.mvps.org/FAQs/Customization/FillinTheBlanks.htm and especially the forms tutorials by Dian Chapman that this article links to. If by "97" you actually mean Word 2007, then you will need to display the Developer tab in order to find the legacy form fields. -- Suzanne S. Barnhill Microso...

Sort Macro 03-24-10
Can I have a Macro that will automatically filter a spread sheet, pick a specific value from a different sheet in the work book and copy the results to a different sheet in the workbook? For example: Sheet 1 has the list of material numbers that are being ran. Sheet 2 has the list of orders for that day with specific material numbers. Sheets 3 - 9 is where the data will be coppied to for each individual line. A separate sheet has been made for each line. I will need to use the data from sheet two 7 different times to gather all the data for each line/material. ...

How can I surpass/modify the Excel limit of 65k rows?
I need to open a document in Excel which exceeds the 65k row maximum per worksheet. The information is in a dif extension, and not xls. Is there a way to bypass or expand the 65k row maximum OR open the data on multiple worksheets? Thanks. "Jacob" wrote: > I need to open a document in Excel which exceeds the 65k row maximum per > worksheet. The information is in a dif extension, and not xls. Is there a way > to bypass or expand the 65k row maximum OR open the data on multiple > worksheets? > Thanks. You might be able to do this in Access, and run queries to ...

Workflow
Hi, I've read a whole bunch of posts on workflow but cannot find the answer to my problem. I want to create a rule that on activation of a Quote a task is created with the task due date set to 7 days prior to the quotation expiry date. Cheers Andy. Sorry, there's no supported way to do this in the current version. The closest you are going to get is to use Wait statements in your workflow. -- Matt Wittemann http://icu-mscrm.blogspot.com "Andy P" wrote: > Hi, > > I've read a whole bunch of posts on workflow but cannot find the answer to > my prob...

Copy Row
Hello, I’d to create a VBA Macro with an “ IF” condition, in my Workbook B in row U14, that would evaluate rows B5 or C5 in workbook “Forecast Template,xls” in order to copy the row with the greatest value. Is that possible? Thanks in advance for any help, Jeff, In cell U14, use the formula =MAX('[Forecast Template.xls]Sheet1'!B5:C5) Change the Sheet1 to the actual sheet name. HTH, Bernie MS Excel MVP "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:27360F5B-5F4B-419D-A712-218F1AE2BD37@microsoft.com... > Hello, > > I'd to create a...

Help! Blank rows??
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I create many data tables in MS Excel for Mac 2008 files which I share with my co-workers. I specifically save the Mac Excel files as windows comma delimited files (.csv) and email to them. When my co-workers open the .csv In Windows Excel 2007 the file shows inserted blank rows between each and every row of data that's in the worksheet. Why does the conversion from Mac Excel to Windows Excel 2007 do this? How do I correct this? Well, I've tried several different methods of getting a Windows .csv ...

Writing a multi-function macro
Bob answered me this morning, but I haven't heard back, so let's try this again. I have a successful macro (reformatting), but I want to add another sub group portion. I don't know how to program it when the macro is recording, so I have to cut & paste. Unfortunatly, when I run the macro, it hits the first End Sub Group & stops, as you would expect. How can I nest a separate command into an existing macro? E-mail me if you have the knowledge & time: LenZGRMich(remove this)@netscape.ne Thanks Len, try something like this Sub macro1() MsgBox "This is macro 1&q...

Still event 9665 after applying /3GB switch and the USERVA setting in the Boot.ini file
We got Exchange Server 2003 with 2,5 Gb RAM. When rebooting Exchange Sever, I still have Event 9665 Warning (memory settings are not optimal). Anyone got a solution to this problem ? I followed Q815372 - How to Optimize Memory Usage in Exchange Server 2003.This is what I did: 1) Registry change - HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager HeapDeCommitFreeBlockThreshold = REG_DWORD 0x00040000 2) Boot.ini change - /3GB switch and the USERVA setting added Thanks Jesper ...

Backing up Custom toolbars and macro buttons
Morning All, I have to backup my works pc and have lots of custom toolbars in Excel, and macro's linked to the toolbar buttons. What files do I need to backup to retrain this for my new pc? Im using office 97, new machine still has office 97. I believe the macros are in personal.xla? Not totally sure but what other files should I save? Many Thanks -- Adam ----------- Windows 98 + Office Pro 97 Adam, Personal.xls holds your (global) macros. Toolbars are stored in a file called Excel.xlb. This file would normally be stored in: C:\Documents and Settings\YourUserName\Application Da...