Add row after column of duplicate info

I want to know if there is a way to have Excel 
automatically insert a blank row after a column if the 
number in the column changes.

Example:
Column B
110011
110011
110011
110345
110347
110347

Would like for it to automatically insert as below:

110011
110011
110011
Insert blank row here
110345
Insert blank row here
110347
110347
Insert blank row here

Can anyone help with this?  It would be greatly 
appreciated :)

Debe
0
anonymous (74722)
4/30/2004 3:21:26 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
533 Views

Similar Articles

[PageSpeed] 31

Data|subtotals can add a line with subtotals between cells with common values.

You can use the outline symbols to show just the subtotal line, 
select those visible rows
hit F5 (edit|goto, ctrl-g)
click special
hit the delete key to clear the subtotals
tools|options|view|uncheck outline symbols.

But I don't like putting blank rows into my data.  But adjusting the rowheight
to be double gives that same effect and makes other stuff easier
(sorts/pivottables/charts).

But here's macro that will insert that blank row.

Option Explicit
Sub testme()

    Dim FirstRow As Long
    Dim LastRow As Long
    Dim iRow As Long
    
    With ActiveSheet
        FirstRow = 1
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        
        For iRow = LastRow To FirstRow + 1 Step -1
            If .Cells(iRow - 1, "B").Value = .Cells(iRow, "B").Value Then
                'do nothing
            Else
                .Rows(iRow).Insert
                'With .Rows(iRow)
                '    .RowHeight = .RowHeight * 2
                'End With
            End If
        Next iRow
    End With
End Sub

You can comment (.rows(irow).insert) and uncomment the with/end with block to
make it double the rowheight.


Debe wrote:
> 
> I want to know if there is a way to have Excel
> automatically insert a blank row after a column if the
> number in the column changes.
> 
> Example:
> Column B
> 110011
> 110011
> 110011
> 110345
> 110347
> 110347
> 
> Would like for it to automatically insert as below:
> 
> 110011
> 110011
> 110011
> Insert blank row here
> 110345
> Insert blank row here
> 110347
> 110347
> Insert blank row here
> 
> Can anyone help with this?  It would be greatly
> appreciated :)
> 
> Debe

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/1/2004 1:30:57 AM
Reply:

Similar Artilces:

Select cheques
When you are building a batch of cheques for one creditor and you add to the batch several payments the payments print out on seperate remittances even though you have specified to print one cheque per creditor. You have to go to edit payments to put all the payments on the one remittance advice. Can this additional funtionality be added to this screen. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, ...

IF/THEN/ELSE adds a blank line in mail merge WORD 2007
Hello, I need to merge some mailings based on if the first name is not the same as nickname, print nickname else don't print anything and go to the next field. The code I have is: {IF {MERGEFIELD fName} <> {MERGEFIELD nName} {MERGEFIELD nName} ""} What is happening is if the first name and nickname are the same a blank line appears. How do I supress the blank line? Make the line break part of your TrueText (note that you don't have to include FalseText if it's "nothing"). So you'll have: {I F { MERGEFIELD fName } <> { MERGEFI...

MOBILE MANAGER ADD ON TRIAL
This is a multi-part message in MIME format. ------=_NextPart_000_0047_01C87AE0.44CE8FB0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit HELLO, DOES ANYBODY KNOW IF THERE IS TRIAL MOBILE MANAGER ADD ON AVIALABLE FOR DOWNLOAD AND WHERE CAN YOU GET IT. WE WOULD LIKE TO TRY SEVERAL, SO WE CAN DECIDE WHICH ONE SUITES OUR NEEDS. ANY COMMENTS WOULD BE APPRICIATED WE ARE A RESELLER OF UNITECH/SYMBOL/DATALOGIC MOBILE DEVICES. THANK YOU. ------=_NextPart_000_0047_01C87AE0.44CE8FB0 Content-Type: text/html; charset="us-ascii" Content-Transfe...

Paste Cells in Column A to Columns B-K
Hi All, I was wondering if someone can help with writing an Excel Macro or some scripting. What I am trying to achieve is the following. In Column A (A1:A1000) there are values of L0xxxxxx xxxxxx=numbers (i.e L0123456) What I want to be able to do is move the values from column A to Columns B-K (10 Columns) 10 values at a time, upto 100 lines. A B C D E F G H I J K L00001 L00001 L00002 L00003 L00004 .5 .6 .7 .8 .9 L00010 L00002 L00011 L00003 L00004 ..5 ..6 ..7 ..8 ..9 L00010 L00011 Any help would be appreciated. T...

Column Chart Thickness of Bars
I need to graph the amount of concrete poured against the planned amount for each week over about 60 weeks. When I create a Column Chart in Excel 2007 the bars in the chart are thin lines, I have tried to format the Data Series and remove the gap width but this does not seem to have much effect. I've also tried increasing the thickness of the border however this just looks stupid and makes the data overlap. Is there any way to fix this? Cheers The bars are narrow because you have one value and six blanks per week. Right click the horizontal axis, choose Format Axis, and under Axi...

Avoid pasting over hidden rows
Hi All I have a quick question in regards to pasting data over a range with hidden rows. Is it possible to paste data over a range of cells and avoid pasting data in hidden rows? Let's say sheet2 has data in range A1:B5 and row 2 is hidden. Let's say I copy range A1:B4 from Sheet1 and paste on Sheet2 starting in cell A1. I would like the data to paste over rows 1,3,4 and 5. (skip hidden row 2) I'm pretty sure the answer is no but I thought I'd ask anyways since there are some clever folks out there who might find a way. (Without using VBA) Thanks in advance. W...

Prevent functions from changing when new rows are added
I have a worksheet configured as a timesheet, where each row represents a task performed. For any given day, I track the task(s) performed and the hours spent on each task, and there are many functions in the worksheet. The worksheet contains the following columns: Column A: blank column (not used for anything). Column B: series of dates (with a blank row in between each date). Column C: tasks performed, manually entered. Column D: hours worked on the task. Column E: hours worked for the week. There's a function in each cell where, if it's Sunday, a sum of the week's...

Contouring data from rows and columns
I have created several contour (surface) plots, but cannot enlarge the size of any of these graphs. I would appreciate any suggestions or help. Thanks, Mangat Mangat - Sometimes when you adjust the 3D view of a chart like this, Excel shrinks it. What it does, really, is shrink the plot area. If you click right near the walls of the chart, not on an axis but well within the outline of the chart object, you will activate the plot area, and it will be surrounded by a thick gray rectangle. You can resize this rectangle. Sometimes it isn't possible to make it exactly the way you w...

Am I able to link excel info with word and have it change when i c
this goes with my other question and problem i need help to do this. i have word and excel 2003 Your question is not clear. Please put the entire question in the body of the message. "Dawn" wrote: > this goes with my other question and problem i need help to do this. i have > word and excel 2003 ...

How do you set up a repeating column in Outlook?
I have three sheets in one workbook, and I want to make changes to one column in sheet one, but I want the changes to apply to the other sheets when I make that one edit. You can group the sheets. Select the first sheet ctrl-click on the subsequent tabs Make your changes. rightclick on one of those grouped worksheet tabs and select "Ungroup Sheets" (Everything you do to one, you'll be doing to all in the group--so be careful!) KellyGrace wrote: > > I have three sheets in one workbook, and I want to make changes to one column > in sheet one, but I want the change...

Last value in column before blank
Hi, My data in one column A:A - Where V100 &V101 are product codes, dates run down the column and Blank is a blank cell befor each new product code. V100 1-Sep-09 2-Sep-09 Blank V101 1-Oct-09 5-Oct-09 10-Oct-09 Blank Question is how do i return the date value for the last entry (eg before the blank) for each product code I want to find. Note also the last date entry is always the max value date. Eg V100 = 2-Sep and V101 =10-Oct. Thks Craig One formulas play ... Assume your source data running in A2 down Put these in B2:D2 In B2: =IF(LEFT(A2)="V",A2,IF(A2=&...

how do I copy specific rows without copying each row individually
I am using Excel 2007. I am trying to copy every 5th row of information and there is no specific criteria met in each row. Can anyone help? for i=1 to 25 step 5 rows(i).copy somewhere next i -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Chris H" <ChrisH@discussions.microsoft.com> wrote in message news:47EE6635-F4CF-4D70-BC02-ADBEB8113D75@microsoft.com... >I am using Excel 2007. I am trying to copy every 5th row of information >and > there is no specific criteria met in each row. Can anyone help? Hi, ...

Title Field
Is it possible to change the column type of the 'Title' field in my SharePoint list for example, to a 'Choice' field? I don't really need a 'Title' field, but of course I need to have the 'Edit menu' option on one of the fields, and I don't want it to be a 'single line of text'. Any help would be appreciated. -- Thanks, Rob E. I wouldn't recommend messing with the title column. It's a system column and it's better to be left alone. What you can do however: 1) Hide the Title column See: http://ferraradata.co...

Concatenate text from columns
How can I concatenate text in colB? ColA has 2 markers C and * Whenever C is followed by * I need to unite the text fields in ColB So, for the data below I want ColA ColB C Text1 Text1a C Text2 Text2a Text2b Text2c ColA ColB C Text1 * Text1a cont'd C Text2 * Text2a cont'd * Text2b cont'd * Text2c cont'd C Text3 Hope this makes sense! Thbe easy method is to use a fromula in the worksheet. Something like this in cell C1 =if(A2="*",B1&B2,"") Then copy formula down column C Next Copy colum...

How do you combine two columns to one? Help!!!!!!
I have address numbers in column A and street names in column B, I want to combine all of both columns into one without going through the whole process for each individual cells. HELP!!!!!! "sttrumpet" <sttrumpet@discussions.microsoft.com> wrote in message news:F72E43CE-CA99-4CD0-BCA1-85EFADCB459D@microsoft.com... > I have address numbers in column A and street names in column B, I want to > combine all of both columns into one without going through the whole process > for each individual cells. HELP!!!!!! Use the fuction =A1&B1 in cell B1 Drag the function ...

BUG? HQ Items Reports returning duplicate items
The HQ Items Master Quantity List and Items Master Price List reports are returning incorrect results. If an item is included in an assembly, that item is listed twice. If an item is included in seven assemblies, that item is listed eight times. I presume that the report is listing the item once and then repeating the entry for each assembly the item is part of. Each entry counts the full inventory, so your totals for cost, etc. are way off. Ideally, assembly items would not appear at all in either of these reports. At worst, a line item for the assembly ILC might appear but with a...

Cannot add sound or video any more to Outlook E-mail (Word Mail) message!
Using Windows XP Pro and Office 2003. Recently updated both to their newest service packs. (including xpsp2). Word (2003) is set as my e-mail editor for Outlook 2003. Before I could add video, audio, or marquee text to an Outlook E-mail message by clicking on the appropriate icon on the "Web Tools" toolbar. Now when I click on one of those icons, I get an error message like the following: ---------------------- The object was created in HTMLInlineVideoCtl. This application is not available to open this object. Make sure the application is properly installed, and that it has ...

Delete duplicates
Hi, I have a list of numbers that contain lots of duplicates like the following: 133150 133150 133150 133154 133154 133154 133155 133156 133156 133156 198355 198355 198355 This list lives in column A. Is there a function I can use to get rid of the duplicates? It's okay if the list is transposed into another column. Thanks for any help. Hi Angie Try Data>Filter>Advanced Filter>Copy to another location>choose Copy To>Unique records only -- Regards Roger Govier "Angie M." <AngieM@discussions.microsoft.com> wrote in me...

Copying row hieghts
Hello, I developed a form that I need to make copies of as I go down the worksheet but when I copy the form and paste it the row heights do not adjust. How can I copy down a new row height? Any suggestions are appreciated. Thanks. --- Message posted from http://www.ExcelForum.com/ Select the whole row and copy it. Then paste. (were you just dragging the autofill button?) "q4u <" wrote: > > Hello, > > I developed a form that I need to make copies of as I go down the > worksheet but when I copy the form and paste it the row heights do not > adjust. Ho...

Can accumulated totals be added to a calculated column in a pivot table?
Let me explain it.. I have a worksheet with data about planned and real values of investment. The data is shown with several columns that describe some properties of the target market. By example Date | Country | Market | Product | Planned Invesment | Real Investment I need to make a Pivot Table that summarize this data and shows the accumulated difference between planned and real investment, and the chance of showing it using any of the dimensions listed above. By Example Date | Country | Planned | Real | Difference | Accumulated Diff. 01/2006 | USA ...

Restricting Inventory Item Adds
I would like to restrict access to the ADD item under the Options drop down in Sales Transaction Entry window to just a select few. What is the best way to accomplish this? I thought I read some place that there existed a chunk file that could be installed per computer that would restrict inventory items from being added. Is anyone familiar with that? You should be able to accomplish that using field level security. If so, then you can restrict it by user rather than by computer. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics.com How about simply restricting acc...

Clear rows where field contains a negative sign
I have a procedure called that looks for a negative sign (*-*) in the D column for all records. If it exists, the entire row is deleted. The procedure below works well for this situation, BUT when there are no records with the negative sign in the D column it deletes the first record in row 1. The Range somehow is set for the value in the D column for row 1. I have been unable to stop this so far. Does anyone have a way to not delete row one if the filter is not met? Here is the procedure: Sub CleanCancelledChks() Dim r As Range With ActiveSheet ActiveSheet.Columns(&...

Moving between multiple columns quickly
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I am trying to move across a spreadsheet from one end (column z) back to column A or last cell with data in it without having to tab all the way. on my pc I would use &quot;end&quot; and an arrow key but my mac keyboard does not have an &quot;end&quot; key. How do I do this? You can use Command + arrow key on the mac. ...

Duplicate Mailbox in Outlook 2003 #2
I am running Outlook 2003 on a Windows XP SP2 machine. Outlook is configured to connect to an Exchange 2003 server. Under the "All Mail Folders" in Outlook 2003, it lists my mailbox twice - one with the Outlook Today icon and one with the Personal Folder icon (even though it is not a personal folder). Clicking on either one displays the exact same contents and folders. I tried going into the email account options to see if I can remove the duplicate account but there is only one Exchange account is listed. I am not running Outlook in cached mode. I do have a PPC I sync with...

Add new record to master and subform at the same time.
Using Access 2003 SP2. I have a master form and linked subform. For procedural reasons related to another form, I need to ensure that a new record is added to the subform when a new record is created in the master form. Obviously, I'm having an issue. Setup: The forms are locked down. The user must click an custom Add button to add a new record in the master form. I'm trying to fit the additional code into this routine. The user can also click another Add button to manually add a record into the subform, but I also need to do this automatically. What is the best way to accomplish...