macro for inserting one row between different data

Hi,

I am using excel 2003, my file looks like below

order no     invoice no   amount
123             S1000         $10
234             S1000         $20
456             S1001         $30
123             S1001         $40

I want to use a macro to insert a blank row to separate different invoice 
no, how to write it ?

Thanks 
eva cheng
0
Utf
4/1/2010 10:04:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
2483 Views

Similar Articles

[PageSpeed] 48

Hi,

I have assumed your invoice numbers are in column B

Sub Insert_Rows()
'insert a row at every name change in a column B
Dim X As Long
Set Sht = Sheets("Sheet1")
MyColumn = "B"
For X = Sht.Cells(Rows.Count, MyColumn).End(xlUp).Row To 3 Step -1
    If Sht.Cells(X - 1, MyColumn) <> Cells(X, MyColumn) Then Rows(X).Insert
Next X
End Sub
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"eva cheng" wrote:

> Hi,
> 
> I am using excel 2003, my file looks like below
> 
> order no     invoice no   amount
> 123             S1000         $10
> 234             S1000         $20
> 456             S1001         $30
> 123             S1001         $40
> 
> I want to use a macro to insert a blank row to separate different invoice 
> no, how to write it ?
> 
> Thanks 
> eva cheng
1
Utf
4/1/2010 10:19:01 AM
I suggest NOT inserting blank rows in you table and simply increase row 
heights instead.



-- 
Regards
Dave Hawley
www.ozgrid.com
"eva cheng" <evacheng@discussions.microsoft.com> wrote in message 
news:818BC1B2-E367-4048-9794-01214BF8AAAD@microsoft.com...
> Hi,
>
> I am using excel 2003, my file looks like below
>
> order no     invoice no   amount
> 123             S1000         $10
> 234             S1000         $20
> 456             S1001         $30
> 123             S1001         $40
>
> I want to use a macro to insert a blank row to separate different invoice
> no, how to write it ?
>
> Thanks
> eva cheng 

0
ozgrid
4/2/2010 1:51:14 AM
Reply:

Similar Artilces:

Problem inserting a column!!!
Hello all. I just accessed a worksheet which Im trying to add a column to... I highlight the column which i want the new column inserted ...and when i try to insert a column it doesnt work! A warning message comes up stating that a column cannot be inserted until blank cells are removed from the right side of the sheet or something to that effect!? Whats going on??? Thank you. -- londonchick ------------------------------------------------------------------------ londonchick's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24529 View this thread: http://www.e...

how do I insert a picture as a background
I am trying to insert a picture in my excel document but I cannot make it go behind the cells where I have already typed some figures and words Nadia Format>Sheet>Background... (It won't print though) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Nadia" <Nadia@discussions.microsoft.com> wrote in message news:AEC4DAE2-08D2-4067-BC00-A8FAB0F727DF@microsoft.com... >I am trying to insert a picture in my excel document but I cannot make it >go > behind the cells where I have already typed some figure...

How to replace PivotTable data fields...?
Hi, I'm progamming a pivot table using VBA. I have no problem manipulating column or row fields, but how can I replace a data field? Everything I try just adds the data field to the existing data field resulting in a mess. The PivotTable.addfields methods *replaces* whatever columns were previously designated as RowFields, ColumnFields, and PageFields with the new specifications, as in: ..AddFields RowFields:="Date", PageFields:="Location" But no matter what I try, I can't replace the data field.... I've even tried setting the Orientation property to xlHidden...

Sum of more than one pivot table
I am working with trying to sum 2 pivot tables. I keep getting extra rows, one named "ROW" with a value of "0" (Zero). I can remove this item from the drop down list but ...... Why is it there? And what can I do to get rid of it? Ken I should have said, I am trying to do this in a new pivot table. Ref: http://edferrero.m6.net/Pivot.html#4 The last part of this tutorial, says to sum the 2 pivot tables. >-----Original Message----- >I am working with trying to sum 2 pivot tables. >I keep getting extra rows, one named "ROW" with a value >of "...

Select rows meeting criteria into another worksheet?
Is this possible? Users will enter records of work into a GeneralEntry spreadsheet. Each record includes a Client field, which they fill using a dropdown. Records are entered as work is completed, so client rows are not grouped together. A set of additional worksheets, one for each client, automatically display only the rows from GeneralEntry which contain the client's name in the Client field. Each of the client sheets should perform summary calculations on just the rows appearing in the client sheet, and display the results above the detail rows. I have tried using filtered l...

access of oracle9i data base with MFC
i m getting some problem while accessing the data base of oracle9i . whenever i m accessing the data base it is always asking password and name , i need to remove this so what should u include in my code that it will privent the same Please, add more details about your problem. It's difficult for us to guess how you are connecting to Oracle database since there are a lot of ways to do it. Jaime "hame" <hame@discussions.microsoft.com> wrote in message news:43299876-F05F-417E-84F0-F51A5A9AF3AC@microsoft.com... > i m getting some problem while accessing the dat...

Two identical graphics, one visible in Draft view, the other not
A Word 2007 document contains several graphics (flowcharts) that were created in Visio (2003), copied to the clipboard (Ctrl-C), and pasted into the Word document (Ctrl-V). I have been working on this document for some time as well as others like it. I usually view these documents in Print View because the graphics are not visible in Draft View (they display as empty white space or the light blue field if selected). Today, I happened to shift to Draft View and discovered that most, but not all, of the graphics were visible. In one case, the same graphic was in the document twice r...

Uniquely Identify Data for Charting with a List Box
Gang, The Internet is a great thing. I found a charting example that does nearly every thing I want to do. Amazingly enough, I was able to figure out how the thing works! The chart is fairly simple. It is a X*Y line chart with 2 series. I found the chart here: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - The chart is called "ChartAgainstStandard.zip." One of the 2 series plotted is a "standard" and the other changes based on the selection made in the drop down list. Amazingly enough, I was able to follow the thing completely and I would like ...

insert worksheet
How do you insert a worksheet after the last worksheet? I just insert the sheet normally, by right-click > Insert > Sheet and this puts it one sheet to the left of my last sheet so then I just left-click on it and drag it over into the proper place at the right end..........if you are copying an entire sheet, then there is an option in the process allowing you to select where to put it...... Vaya con Dios, Chuck, CABGx3 "Katie" <Katie@discussions.microsoft.com> wrote in message news:28AD09FA-FF11-4E85-928B-EB973AFFFBD9@microsoft.com... > How do you insert a workshe...

help with spliting data cells
I am looking for assistance with splitting data between cells. I have a column of data (first (space) last name). I need to split that column into two seperate columns so each name value is in it;s own cell and i end with 2 seperate columns. (john smith) to (john) (smith) Please advise if this is easy and can be done. Thanks, Phil pcavalcanto@Aol.com -- pcavalcanto ------------------------------------------------------------------------ pcavalcanto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32212 View this thread: http://www.excelforum.com/showthread...

How do I create a graph from an equation and not a set of data?
How do I create a graph from an equation and not a set of data? Hi, Stephen Bullen has an example. See ChtFrmla within the Charting section. http://www.oaltd.co.uk/Excel/Default.htm The example file is, http://www.oaltd.co.uk/DLCount/DLCount.asp?file=ChtFrmla.zip Also see Tushar Mehta's Plot manager, http://tushar-mehta.com/excel/software/plot_manager/index.html Cheers Andy ashemorry wrote: > How do I create a graph from an equation and not a set of data? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

what is short cut key for running the macros
please prvide the answers intime Which macros? If you're talking about custom macros in a workbook, (Excel 2003) use Tools Macros to bring up the list of macros and select one and use the [Options...] button to assign a short-cut key sequence to it. Do much the same from the [Developer] tab in XL 2007. "srinivas" wrote: > please prvide the answers intime ...

How to wipe out HQ and SO data without the setting and paramters
Hi. Im planning to wipe out my database without clearing out its parameters and settings. I have an existing Store running on HQ and SO. I plan to change the barcodes for all items as they are inconsistent. Therefore i need to wipe out everything without the parameters and configuration settings. Can This be done? If so i need a detailed steps in doing this. This sounds very dangerous. What if you changed all your items so they start with a 'Z-' and then make all those items InActive, that way will not lose any Sales History? Then delete everything from the Alias table if you ar...

exchange 2003 public folder sync, which one is updated
If I have public folders replicateing between 2 exchange 2003 systems and someone makes a change, which one is updated? The one they are connected to is the one that is updated. Users are connected to the one referred to in the properties of the Mailbox Store that houses their mailbox. "Trey" <Trey@discussions.microsoft.com> wrote in message news:271997FD-E0BD-469B-A64D-2FC52FB68575@microsoft.com... > If I have public folders replicateing between 2 exchange 2003 systems and > someone makes a change, which one is updated? ...

Cannot edit data in forms but can in tables
I have a problem! I created a database where I can only edit the data in tables. In the forms I created I can see the data but cannot edit the data. I am using 2007 that I have set so the file can be opened in 2003. The data was imported to create tables from two Excel spread sheets using the wizard. The tables are linked together and the combined data is shown in a report. This all works without a problem. Now I need to edit the data, so I created several forms all of which I cannot add, delete or edit the data. I saw under the User and Group Permissions that u...

Excel data query goes away.
I have a work book that gets information from an SQL database. About every two months the query goes away. Why? and how can I stop this from happening? ...

insert data from one excel file into another
Is there any way to insert data from one excel file into another without doing copy and pasting? I need to automate the process of inserting data but I cannot use copy-paste, because it puts data on a clipboard, and my Excel VBA program runs in a multi-user environment. So if one instance of a program is copying data into clipboard, and another instance is pasting it at the same time, it will paste incorrect data. Thank you Leonard. You'll need both spreadsheets open, but try this; Option Explicit Sub MyMacro() Dim MyVariable As String Windows("File2.xls").Ac...

how to prevent a formula in a summary sheet from automatically updating when a row is addedto the referenced sheet
I have a workbook set up that consists of several sheets and a summary sheet that refers to the third row on every other sheet. The worksheets that are referenced contain dated information that is sorted with the most recent entries entered in row 3 and these are the entries I need to track in the summary sheet. When I insert a new row 3 in these worksheets, the formula in the summary automatically updates to line 4 and I need it to always reference row 3, the newest entry. This should be simple but I cannot find the solution. Any ideas? Thanks, krisp1950 You could use the INDIRECT fun...

Pivot Table
I have 2 lists of 100 items (rows) with 5 data elements. Each list has a common element, which can tie the two lists together. How can I combine these 2 lists and use it for the source data of a pivot table? Todd ...

Insert Picture fails repeatedly in Word 2008 when working with labels
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel When trying to insert a picture (.tif, .jpg, .gif) into a text box on a label document, Word 2008 repeatedly fails, giving the message: "This application cannot open this file. This file is an unsupported graphic format or may be damaged. Try opening this graphic in another application". If I switch back to the regular (non-label) document, I can successfully insert the picture, then I was able to copy and paste it. But after a couple of times doing this, I received the message no matter which document I tried...

How can I import my data from Entourage for Macosx
I a moving from a Apple Mac computer and I want to move all my data from Entourage to Outlook Ken Allen <Ken Allen@discussions.microsoft.com> wrote: > I a moving from a Apple Mac computer and I want to move all my data > from Entourage to Outlook See if this helps: http://www.entourage.mvps.org/cross_platform/ -- Brian Tillman "Ken Allen" wrote: > I a moving from a Apple Mac computer and I want to move all my data from > Entourage to Outlook. Will these scripts move all my e-mails and attachments over to Outlook as well. Thank you for all your help. I h...

removing 0 value data labels
Hi All How would I remove 0 Value data labels from a chart using vb script? No need for VBA. You can make a custom number format for the labels. Select the whole set of labels, press CTRL+1 to format, and on the number format, select Custom in the left hand list, and add a format like one of these: 0;0;;@ 0.0;0.0;;@ There are four items in a custom format, by default the formats for positive, negative, and zero values, and for text. Leave off the format for zeros, and the label will not appear. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and C...

delete row if given cell = 'X'
Is there an easy way to do the above. Thanks much Carl "MSNews" <nf@no.com> wrote in message news:%23Fj6fBvPKHA.764@TK2MSFTNGP02.phx.gbl... > Is there an easy way to do the above. > > Thanks much > > Carl > I'm not an expert but use data filter to select the rows, select and delete them. Try it with a trial sheet first though! Peter Several ways 1. data>filter>autofilter>filter on the x>delete 2. a looping macro from the bottom up sub delifx() for i=cells(rows.count,"a").end(xlup).row to 2 step -1 if lcase(cells(i,&qu...

Data migration manager error
Hi, I am running migration manager for accounts, I am getting error on importing the file that - data migration manager cannot run furter, close and try again. I have tried several times , it is not functioning.Is there any service to be started. regards Ritesh ...

Automatically inserting symbols
Dear all I have created an Excel worksheet (97) and need to enter a "tick" in several cells. In Word, you can apply a short-cut to a symbol so you can enter it quickly but I can't find a similar function in Excel. Is it possible to use shortcuts and, if so, how do you create them? Thanks. Louise Here's a macro to do the job:- '--------------------------------------- Sub TICK() ActiveCell.Value = Chr(252) With ActiveCell.Font .Name = "Wingdings" .Size = 10 .Bold = True End With End Sub '---------------------------------------- --------------------...