Excel macro to insert rows if a cell does not equal the value above that cell

I have a spreadsheet listing columns of information for many different
people.  If more than one row exists for the same person, I'd like to
insert 2 rows after the row so that the group is separated from the
next group.  For example, here's how the data appears now:

LastName    Account#    Balance
Martin         1         500.00
Martin         2         750.00
Smith          5         100.00
Thomas         9         900.00

Here's what I'd like it to look like after running the macro:

LastName    Account#    Balance
Martin         1         500.00
Martin         2         750.00


Smith          5         100.00


Thomas         9         900.00

Any thoughts?

Thanks a bunch for the help!
0
8/12/2004 2:40:21 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1188 Views

Similar Articles

[PageSpeed] 51

If you want to SEE extra space (rather than actually HAVE 
2 blank rows), then changing the row heights for Smith and 
Thomas would do the trick, and this can be done easily 
with formulas. In an unused column, say in G3, enter:
=IF(G2<>G3,1,true) and fill down as far as necessary.
Then select column G, use Edit/Goto Special, select 
Formulas and DESelect text,logical,and Errors (leaving 
only numbers), then click OK. Now, all the 1's are 
selected. With this selection, go to Format/Row/Height, 
and change the height to 30, then clear (or hide) column G.


>-----Original Message-----
>I have a spreadsheet listing columns of information for 
many different
>people.  If more than one row exists for the same person, 
I'd like to
>insert 2 rows after the row so that the group is 
separated from the
>next group.  For example, here's how the data appears now:
>
>LastName    Account#    Balance
>Martin         1         500.00
>Martin         2         750.00
>Smith          5         100.00
>Thomas         9         900.00
>
>Here's what I'd like it to look like after running the 
macro:
>
>LastName    Account#    Balance
>Martin         1         500.00
>Martin         2         750.00
>
>
>Smith          5         100.00
>
>
>Thomas         9         900.00
>
>Any thoughts?
>
>Thanks a bunch for the help!
>.
>
0
anonymous (74722)
8/12/2004 3:07:39 PM
JSD,

In the macro below, change the myCol = 3 to the column number that you want
to base the insertion on. A=1 , B=2, etc.

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
End If
Next myRow
End Sub



"JSD" <jasondugdale@yahoo.com> wrote in message
news:4783e1a4.0408120640.5da1c269@posting.google.com...
> I have a spreadsheet listing columns of information for many different
> people.  If more than one row exists for the same person, I'd like to
> insert 2 rows after the row so that the group is separated from the
> next group.  For example, here's how the data appears now:
>
> LastName    Account#    Balance
> Martin         1         500.00
> Martin         2         750.00
> Smith          5         100.00
> Thomas         9         900.00
>
> Here's what I'd like it to look like after running the macro:
>
> LastName    Account#    Balance
> Martin         1         500.00
> Martin         2         750.00
>
>
> Smith          5         100.00
>
>
> Thomas         9         900.00
>
> Any thoughts?
>
> Thanks a bunch for the help!


0
Bernie
8/12/2004 3:09:11 PM
I'd just like to add that this solution is the one I prefer. Adding blank rows
can create all sorts of headaches with formulas, etc.

On Thu, 12 Aug 2004 08:07:39 -0700, "Bob Umlas Excel MVP"
<anonymous@discussions.microsoft.com> wrote:

>If you want to SEE extra space (rather than actually HAVE 
>2 blank rows), then changing the row heights for Smith and 
>Thomas would do the trick, and this can be done easily 
>with formulas. In an unused column, say in G3, enter:
>=IF(G2<>G3,1,true) and fill down as far as necessary.
>Then select column G, use Edit/Goto Special, select 
>Formulas and DESelect text,logical,and Errors (leaving 
>only numbers), then click OK. Now, all the 1's are 
>selected. With this selection, go to Format/Row/Height, 
>and change the height to 30, then clear (or hide) column G.
>
>
>>-----Original Message-----
>>I have a spreadsheet listing columns of information for 
>many different
>>people.  If more than one row exists for the same person, 
>I'd like to
>>insert 2 rows after the row so that the group is 
>separated from the
>>next group.  For example, here's how the data appears now:
>>
>>LastName    Account#    Balance
>>Martin         1         500.00
>>Martin         2         750.00
>>Smith          5         100.00
>>Thomas         9         900.00
>>
>>Here's what I'd like it to look like after running the 
>macro:
>>
>>LastName    Account#    Balance
>>Martin         1         500.00
>>Martin         2         750.00
>>
>>
>>Smith          5         100.00
>>
>>
>>Thomas         9         900.00
>>
>>Any thoughts?
>>
>>Thanks a bunch for the help!
>>.
>>

0
anonymous (74722)
8/12/2004 6:29:25 PM
Reply:

Similar Artilces:

Multiple hyperlinks in one cell
Hey, I'm stuck with an excel problem which I cannot solve. I want to pu different messages in one cell and add a hyperlink to some of thes messages. For example I want to put this information all into one cell "message1 - message2(with hyperlink) - message3(with hyperlink) message4" I really hope it is possible to create multiple hyperlinks within on cell, but for now I can only add a hyperlink to a cell. If you would know how I could fix this issue I would be reall gratefull. Thx in advance, Veroniqu -- Message posted from http://www.ExcelForum.com If it's really...

VBA from another app: Suppressing Excel confirmation dialog?
After creating/formatting several worksheets from MS Access, I'd like to delete the "Sheetn" worksheets that got put there when I did a .WorkBooks.Add. I avoided using them because I'm not sure how/why they are created - i.e. maybe some user's defaults would only create 1 empty sheet or none. So, form MS Access's VBA I'd like to do: On Error Resume Next .Worksheets("Sheet1").Delete .Worksheets("Sheet2").Delete .Worksheets("Sheet3").Delete .Worksheets("Sheet4").Delete On Erro...

how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...

How to get SUMPRODUCT on filtered cells
I'm currently using the formula =SUMPRODUCT((F2:I1475>=1)*(F2:I1475<=9999)) This gives me the number of the full range of cells but I need this to update whenever I use a variety of filters. Any help is greatly appreciated Sam.D Say we have data in A1 thru B29 like: flia value 0 pass 2 pass 1 pass 2 pass 1 pass 2 pass 0 pass 2 pass 1 pass 2 pass 1 pass 0 pass 2 pass 0 pass 1 pass 1 fail 1 fail 0 fail 2 fail 1 fail 0 fail 1 fail 2 fail 1 fail 1 fail 1 fail 1 fail 2 fail and are appling autofilter to column a. We want to use sumpr...

Comments in Cells disappear after time
A colleague has a spreadsheet with lots of comments in the cells and apparently after a while the comments 'disappear'. Does anyone have any ideas why this happens? Could it be there is a maximum number of comments, or do they have a life expectancy? The sheet is opened in Excel 2000 & Excel 2003 depending on the user. Thanks for your help. Comments don't normally disappear on their own. Perhaps your colleague is accidentally deleting the rows or columns in which the comments occur. Or comments could disappear if another cell, with no comment, is dragged onto a cell ...

Opening a new instance of Excel
I am using multiple monitors for work and it is great! Is there a setting that I can use so that it opens each new excel file in a new excel window so I can drag different ones to each monitor? Is there a similar setting for Word? I am using Excel 2002 and Word 2002. Thank you. Hi, Yes, you can check the Windows in Taskbar checkbox in Tools; Options. This is on the View tab for both Word and Excel. >-----Original Message----- >I am using multiple monitors for work and it is great! Is >there a setting that I can use so that it opens each new >excel file in a new excel ...

Importing Data into an Excel Pivot Table via Access
I have set up a query in Microsoft Access which is linked to our AS400 server. I have created pararmeters within Access which asks for certain fields which works. I then go into Excel and create a pivot table with the external data source that I have created in access. When I go to enter a pararmeter within Microsof Query I get a reply saying that "Parameters can not be used with this Query", what I want to do is setup a parameter on the Excel spreadsheet which then goes and gets the data i require from this parameter. I would be very grateful if someone could help me with thi...

Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...

Value of Less Than Zero to Equal Zero
Hello: I am trying to format my answers in cells of a worksheet. I have an equation that uses addition, subtraction, multiplication and division. This equation has dependent variables in other cells and when these cells are filled in with data, the original equation yields a number. Unfortunately, if one of the cells is not used, there is still a value reported by the original equation. As an example: If B2=((A2*3)+(A3)-1.5) and A2=2 and A3=1, then the result will be equal to 5.5. But if cell A2 does not have a value, the value will report -0.5. Here is the problem. Well, I think I have t...

Is it possible to add a base line in excel
I have a graph the tracks test scores. I want to establish in a bar chart a base line for acceptable test scores. I can do this by drawing a line on the chart but I want to be able to include this in the chart itself. Hi see: http://andypope.info/charts/averageline.htm http://www.tushar-mehta.com/excel/charts/straight_lines/index.html http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine -- Regards Frank Kabel Frankfurt, Germany Tom King wrote: > I have a graph the tracks test scores. I want to establish in a bar > chart a base line for acceptable test scores. I can do th...

go to next cell and paste question
I am writing a macro that is supposed to take certain cells from a "interface" page, remove them, and paste to a string of cells o another. That is easy, but I cant figure out how to make them go t another set of cells, if the ones I recorded are filled. Example: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 8/4/2004 by Lane Lacy ' ' ActiveWindow.LargeScroll ToRight:=17 ActiveWindow.ScrollColumn = 239 ActiveWindow.SmallScroll ToRight:=10 Range("IV2:IV7").Select Selection.Copy Application.CutCopyMode = False Selection.Cut Sheets("productivity&qu...

Emailing in excel 2003 02-26-10
If i type in the cell A34: neil.Holden@test.com and press a button is it possible to email to the address of what ever is in A34 is? The email body should say: this has been submitted for cell B34 and todays date. Thanks. Check out Ron De Bruins "Send-Mail" tips: http://www.rondebruin.nl/sendmail.htm Micky "Neil Holden" wrote: > If i type in the cell A34: neil.Holden@test.com and press a button is it > possible to email to the address of what ever is in A34 is? > > The email body should say: this has been submitted for cell B34 and...

Assigning a Hyperlink to a Macro
I am using Excel 2003. Is it possible to assign a hyperlinked cell to a macro within the same workbook? Paul Hi not really sure what you're trying to achieve. What should happen exactly? -- Regards Frank Kabel Frankfurt, Germany "Paul" <Paul@discussions.microsoft.com> schrieb im Newsbeitrag news:DB4AD3A6-F6E2-4F4B-9898-21E01E010624@microsoft.com... >I am using Excel 2003. > > Is it possible to assign a hyperlinked cell to a macro within the same > workbook? > > Paul when you select the hyperlink, the macro would run. "Frank Kabel" ...

re: updating values
that works, but i'll need to add a lot of hidden feilds (20+/-)... Is there another way (perhaps more efficient -if not as simple?) ("there's more than one way to skin a cat") thanks inadvance, mark --------------------------------------------------------------------- "Daryl S" <DarylS@discussions.microsoft.com> wrote in message news:79CFD708-34B3-419A-A3F1-CF7050ACDE9F@microsoft.com... > Mark - > > Add the field [PresetOption] to the form. You can set the .visible > property > to FALSE so the user won't see it. Then the code...

Macro must return the name of the button
Hi there, I want to a macro to display the name of the button (or object) from where I am calling that macro. Anyone an idea? thanks, Derek What sort of button (userform, worksheet forms, worksheet control toolbox)? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Derek Brussels" <Derek Brussels@discussions.microsoft.com> wrote in message news:8441F83B-89DC-4515-A643-CE8F258DFC1F@microsoft.com... > Hi there, > > I want to a macro to display the name of the button (or object) from where I > am calling that macro. Anyone an idea? > &...

Subtracting value from main form
I have a borrow module which will alow user to return item separately. So, I have get the structure of returning it separately. In my main form is the borrowing item, with the loaned quantity and the owed quantity (will be calculated). In the subform, there is the returning transaction. User will need to key in the quantity returned and it will be automatically deducted from the quantity owed. But how am I supposed to get the quantity deducted while it 1 is in main form and the other is in subform? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-fo...

Max Value
I have a sent worksheets that supply data. HOwever I would like to set up a VLookup that looks by Month and Max Value. For Example Column A is Jan, Feb, March, Feb, Jan, Mar and Column B is 42, 18, 22, 38, 45, 11. I want to look for particular month and highest value in that month How about this: =MAX(IF(A1:A30="Jan",B1:B30)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) (and adjust the ranges accordingly) Ramon wrote: > > I have a sent wor...

Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...

match two conditions to return value from pivot table
Pivot table setup with name in row field, week number in column value. Need to lookup/match the week number then the name and return a 1 value from the pivot table. Is this possible? example: 1 2 3 Sales 1000.00 3000.00 Cost of Sales 350.00 1000.00 200.00 Accounting fees 50.00 10.00 30.00 Advertising 20.00 ...

Datagridview Combobox Values
Good Afternoon All, I have a datagridview dgv1 that has a combobox column in it. Each row should have an individual item to select from in the combobox. My code displays the same values in all of them. Any help would be great! Here's my code: Dim dsMP As DataSet = New DataSet() Dim intRow As Integer = 0 Dim intRows As Integer = dgvRO.Rows.Count Dim strPartNumber As String = "" Dim dt As DataTable = Nothing Dim dr As DataRow = Nothing For intRow = 0 To intRows - 1 strPartNumber = dgvRO.Rows...

Does anyone have a dashboard gauge (speedometer style) for Excel?
I am trying to create dashboard charts from Excel data and would love other templates not available in Excel today - speedometer charts, multi-dimension comparitive charts, charts that build information overlays. I regularly create these in a manual way for executive and customer summaries but would appreciate the ability to automatically generate these types of charts allowing for real time viewing of "what if" scenarios. Steve, there are tons of these things out there to review, few better than this collection: http://www.andypope.info/charts.htm Andy Pope has put together...

Merge Cells #5
I know how to merge two cells using formula =A1&B1, but can someone tell me how to insert spacing between the two within the new cell. I'm merging a part number and description, and need spacing between them. Use =A1&" "&B1 the " " is showing that you want to insert text, and that the text is a space character. It could be "-" or ".", etc. "R. Stevens" <R. Stevens@discussions.microsoft.com> wrote in message news:B02E936E-5947-4F1B-A8E5-F5A30343E549@microsoft.com... > I know how to merge two cells using formula =...

Excel Edit F2 button changed for Mac???
Switched to Microsofts version of Excel for Mac. Can anyone tell me what keystroke allows me to edit a cell? Before I switched to a Mac it was the F2 button. Please help. Thank you. See the answers in the m.p.mac.office.excel newsgroup. In article <1176582208.958694.269620@q75g2000hsh.googlegroups.com>, ssears@indy.tds.net wrote: > Switched to Microsofts version of Excel for Mac. Can anyone tell me > what keystroke allows me to edit a cell? Before I switched to a Mac > it was the F2 button. Please help. Thank you. ...

EXCEL TROUBLESHOOTING #2
I have an excel file (2000 format), that after I made a number of changes is causing me problems when I re-open the file. Windows task manager goes to 100% CPU activity, and i cant do anything within the excel file. However, if I set recalculation to manual before I open the file, all seems fine. Obvioulsy I have a problem. But how do i find that problem ? Thanks in advance. I have had some experience running large spreadsheets lately. Above a certain size, the recalculation time seems to climb very fast. While Excel is recalculating, you can't do anything anyway. Best in my v...

macros disabled
I've just upgraded to Office 2007. Now, when I tried to run a program, it says, "The macros in this project are disabled." What to do? Thanks, Scott I've got it. It was due to the "Missing" references. Mr. Chip Pearson helped identify the problem. "Scott" wrote: > I've just upgraded to Office 2007. Now, when I tried to run a program, it > says, "The macros in this project are disabled." What to do? > > Thanks, > Scott Just as with 2003 you need to enable macros. But, unless the VBIDE is open, you don&#...