Insert new row as cell contents change

Insert new row as cell contents change. After importing 
data I have a spread sheet with a column that contains a 
series of alpha numeric characters. At various random 
intervals in this column the contents change. EG rows 1 to 
4 could contain ABC, then rows 5 to 15 could become 222. I 
am looking for a method to insert a blank row 
automatically between the rows were the contents change. 
Many Thanks
Geo

0
anonymous (74722)
1/26/2005 9:27:29 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
503 Views

Similar Articles

[PageSpeed] 14

George

If you are familiar with VBA the code below will do what you want. 
Preselect the column of data first

Sub InsertRowAfterValueChange()
Dim myCell As Range
Dim sCurrVal As String
sCurrVal = ActiveCell.Value
For Each myCell In Selection
    If myCell.Value <> sCurrVal Then
        myCell.EntireRow.Insert
        sCurrVal = myCell.Value
    End If
Next myCell
End Sub

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"George" <anonymous@discussions.microsoft.com> wrote in message 
news:22c501c50389$416d1d40$a501280a@phx.gbl...
> Insert new row as cell contents change. After importing
> data I have a spread sheet with a column that contains a
> series of alpha numeric characters. At various random
> intervals in this column the contents change. EG rows 1 to
> 4 could contain ABC, then rows 5 to 15 could become 222. I
> am looking for a method to insert a blank row
> automatically between the rows were the contents change.
> Many Thanks
> Geo
> 


0
1/26/2005 10:19:48 AM
Nick, Many thanks, it worked perfectly.
>-----Original Message-----
>George
>
>If you are familiar with VBA the code below will do what 
you want. 
>Preselect the column of data first
>
>Sub InsertRowAfterValueChange()
>Dim myCell As Range
>Dim sCurrVal As String
>sCurrVal = ActiveCell.Value
>For Each myCell In Selection
>    If myCell.Value <> sCurrVal Then
>        myCell.EntireRow.Insert
>        sCurrVal = myCell.Value
>    End If
>Next myCell
>End Sub
>
>-- 
>HTH
>Nick Hodge
>Microsoft MVP - Excel
>Southampton, England
>nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>
>
>"George" <anonymous@discussions.microsoft.com> wrote in 
message 
>news:22c501c50389$416d1d40$a501280a@phx.gbl...
>> Insert new row as cell contents change. After importing
>> data I have a spread sheet with a column that contains a
>> series of alpha numeric characters. At various random
>> intervals in this column the contents change. EG rows 1 
to
>> 4 could contain ABC, then rows 5 to 15 could become 
222. I
>> am looking for a method to insert a blank row
>> automatically between the rows were the contents change.
>> Many Thanks
>> Geo
>> 
>
>
>.
>
0
anonymous (74722)
1/26/2005 11:47:14 AM
Reply:

Similar Artilces:

new to outlook
Hi, I am using Outlook 2003 and a new user. We had a limited number of files or email to be stored in outlook. I am creating a Folders under Inbox so I will just transfer manually what I have receive email from my colleagues. Is there a way telling Outlook to transfer automatically to Folders I have assigned? Do I need a VBA code to accomlish it? Any suggestion or advise is much appreciated. thanks You can transfer automatically through the Rules function in Outlook. Inbox | Tools | Rules and Alerts. New Rule. Regards Judy Gleeson MVP Outlook "R...

Replace a comma with a period in a cell containing a lastname, first name, middle i
Hello - I am trying to clean some data and need to change all of my names from McLaughlin, Victor, (i.e, comma) W to McLaughlin, Victor.(i.e., period) W Is there an extract and replace formula or method of som sort (in excel or access) that will allow me to pull the first comma from the right and replace it with a period. Thanks for any suggestions! Select the cells you want to change and run this tiny macro: Sub comma_tose() For Each r In Selection v = StrReverse(r.Value) r.Value = StrReverse(Replace(v, ",", ".", 1, 1)) Next End Sub For example: a,b,c,d wi...

Change the working directory for a MFC application
I am trying to change the working directory of a MFC application after the application execution begins. Chdir or setcurrentdirectory doesnot work for a MFC application. Can anyone suggest how i can do this? Thanks, - Akila. Akila wrote: > I am trying to change the working directory of a MFC application after > the application execution begins. > > Chdir or setcurrentdirectory doesnot work for a MFC application. > > Can anyone suggest how i can do this? > > Thanks, > - Akila. > SetCurrentDirectory does work for a MFC application. If it doesn't seem ...

Standard Cost Changes Screen
I'm trying to enter a proposed standard cost on a finished good item but the Proposed Standard Cost Field is greyed out. Cards >> Manufacturing >> Inventory >> Standard Cost Changes. Any ideas as to why it's Greyed Out, and how can I fix this? regards, davidv Is the Bill of Materil a "Phantom" item? "tintin91" wrote: > I'm trying to enter a proposed standard cost on a finished good item but the > Proposed Standard Cost Field is greyed out. > > Cards >> Manufacturing >> Inventory >> Standard Cost Changes. &...

Problems with re-setting the last active cell in an Excel workshee
I am trying to re-set the last active cell on an Excel 2002 worksheet (in this particular sheet it should be cell DA197). I have used both the methods described in the Knowledge Base article (deleting rows and columns and re-saving; and the Excess Format Cleaner add-in). Deleting the rows and columns does not work; using the Excess Format Cleaner does not work either and it then also hides the rows from 198 to 65536 - but does not do the same for the columns. I have checked that there is no protection on the worksheet. Has anyone else come across this problem and if so can you please ...

MS Excel file extension changed through e-mail
MS Excel .xlsx files sent as attachments are received without the x, as a .xls instead. The same is true for MS Word .docx changed to .doc Answered in the Word group. Please don't post the same question in multiple groups. It not only causes responders to waste their time replying to questions that have already been answered but it also makes it more difficult for you to follow up on the replies. If it truly is an issue that involves more than one app either post to the Office group rather than the individual groups or use a newsreader rather than the web interface & learn to ...

Cannot change account type.
I have an HSA with a credit union that is updated online, I want to have it listed under the Investment Accounts instead of Bank Accounts. The option to change the account type is greyed out with the message "...online-enable account cannot be changed" Was there a question here? I think the message you got tells you about three fourths of the problem. The final fourth that the first three fourths prevents you from getting to is that an Investment Account and a Bank Account are fundamentally different types--one holds Investments, the other holds cash--so a change from one ...

Combine multiple rows into one row with multiple columns
Hi, I have a table set up so that there are three columns: StudyID, DrawDate, and Value. StudyID and DrawDate are the primary key. I want to create a table from this one that has only one row for each StudyID so that it would go from: StudyID DrawDate Value to StudyID DrawDate1 Value1 DrawDate2 Value2 DrawDate3 Value3 etc. Is there a way to do this? Thanks, Elysia "Elysia Larson" <elysia.larson@gmail.com> wrote in message news:832e952f-174f-489f-ab7a-e2189f660a92@f6g2000vbp.googlegroups.com... > Hi, > > I have a...

Add Word and change format
1) Let say colomn A is a product codes, such as "PK0021", "UQ05P8", etc...Now I want add a "Z" in front the codes. To be "ZPK0021, ZUQ05P8". What's the faster way in case I got thousand of codes? 2) In my colomn B is such code as "18-521-65, 18-81-84, 18-1112-65" and etc. Now I would like to make it to be standard to 4 digit for the middle number to be "18-0521-65, 18-0081-84, 18-1112-65" ... As the same senario as above, I got more than thousand of such codes... What's the faster way? Kelvin The first could be done wi...

Column to Rows
I want to convert my data from one column into rows. I have my data set up now as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 I want it to be displayed into 4 separate columns as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 Please advise, thanks, Don -- Don D. ------------------------------------------------------------------------ Don D.'s Profile: http://www...

Charts switch from 'Series in Rows' to 'Series in Columns'
I use VBA to create charts in Excel 2003, but find that sometimes the Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not intended), even if I have specified 'Series in Rows'. This happens intermittently, and I am not sure what I am doing wrong. I do save the workbook as Microsoft Excel 97 so that a user with Excel 2000 or Excel 2003 can use the workbook. Thank you for any suggestions. Hard to tell if you keep the code secret. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peac...

tasks to cell phone
How can I use exchange to send tasks to cell phone. I want to do when f.e. this phone is away from the office. This is PDA phone. On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" <fwitkowski@gmail.com> wrote: >How can I use exchange to send tasks to cell phone. I want to do when >f.e. >this phone is away from the office. This is PDA phone. If you're using a Smartphone you can sync tasks already with ActiveSync. "Mark Arnold [MVP]" <mark@mvps.org> wrote: >On 14 Dec 2005 11:38:11 -0800, "Filip - beginner" ><fwitkowski@gmail...

Replace Cells with Column names in functions?
I have a # of fairly long/complex cell functions that get hard to debug because there are also a lot of rows. Is there anyway to change display so it replaces the column name e.g. If(BT1204="X". BA="Y" to If(CustomerName="X", CustomerCode="Y") ? CustomerName is a defined name range for BT1204 Find & Replace Find what: BT1204 Replace with: CustomerName "msnyc07" wrote: > I have a # of fairly long/complex cell functions that get hard to debug > because there are also a lot of rows. > > Is t...

Serious performance issues when changing roles
Just a heads-up: don't change roles during times of heavy user activity or you may have severe performance issues. When you change a role, the MSCRMSEcurityService evidently kicks off a massive job that updates the securitydescriptor field in every record of every major object. Last time I did this, it attempted to obtain a table lock, and as a result, brought my system to its knees. The process was trying to update 46000+ rows from the annotationbase table (you can see this by looking at the last SQL command for the process that is blocking other processes). Evidently bec...

Change Withdrawal/Deposit to Transfer
Hi folks. How do you change a transaction that's already been recorded from a Withdrawal or Deposit to a Transfer? Thanks. Dave I'm assuming you are using Transaction Forms. Most people who have used Money for a long time would advise just turning them off. You can also change the category to Transfer: or one of the Expense categories. There is also For a WD -> Transfer switch the Transfer seems Options|Change Transaction To. <bvdesk@gmail.com> wrote in message news:1175621813.359866.84060@b75g2000hsg.googlegroups.com... > Hi folks. How do you change a transaction...

The Insert Hyperlink button is grayed
For just one user in our office - seems she's got the same settings as I do, but she can't use the Insert Hyperlink option. She's using Word as her email editor, and using HTML format. We've checked the AutoCorrect options in Word and all are the same as mine. Any ideas? ...

Calendar in Cell Validation
I want to implement a cell validation such that when the user attempts to input a date, a "list" box-like functionality pops up that has a calendar and the user may then choose the date by picking with the mouse How would I implement this? Thanks Jerry Try the following Web site. http://www.fontstuff.com/vba/vbatut07.htm This site's author covers this in a tutorial, but also provides downloads. Mark <jerry.ranch@pioneer.com> wrote in message news:2r9t51pjmumjk7rjpopo7fuamg81gqkljq@4ax.com... >I want to implement a cell validation such that when the user attempts &g...

How do I change the text size in a drop down box
I am using excel 2003. It seems to default to 10pts. Changing the size in the source list or the default in the general tab did not change the size. ...

extract info from cell, then count
I have a 2-part question: (i) I have 1000's of e-mail addresses but want to extract the countr from the e-mail i.e. abc@def.de, where de (Germany) is needed. How d I isolate the ".de" (and others eg .fr, .edu, .com etc etc) (ii) Having done the above, I then need to do a count. Rather than us COUNTIF and include the code for every country in the world, is ther any other way of counting? I guess a Pivot table? thanks, cathal..... -- Message posted from http://www.ExcelForum.com One way: =MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LE...

Can I use VBA to add cells (over blanks) then do multiplication
I have a Word table in which the last column contains numbers (3 and 4) and some bank cells and I want it add them and put the total into the second last row (7 in this case). The last row contains a multiplier (3) which when applied to the total results in 21. Below is the table. | | | 3 | | | | | | | | 4 | | | | 7 | | | 3 |21| How can I achieve this in VBA (under Word 2003 and 2007) remembering that the user can add rows to the table and the last column can contain blank cells. Thanks in advance for any assistance, Peter Evans Sub ScratchMaco(...

insert a JPEG into EXCEL 2002
Hello - I was asked to "pretty up" an Excel invoice for work and need to place a JPEG into the file. I know I can't insert a picture into the header/footer with Excel 2002, but can't I just insert it into the body of the file? In Excel Help it simply says to click on Insert Picture, but for some reason the Insert Picture option is ALWAYS greyed out. Any suggestions? I would REALLY appreciate it! Cheers! Hi mckee You can insert a picture into the header / footer with XL 2002. Go to File>PageSetup. Select the "Headers / Footers" tab and select "Custom ...

Row Grand Totals in Pivot Tables?
I'm working in Excel 2007 and I can't seem to see my row grand totals in my pivot table. I can see the grand totals on the columns, but no rows. Any ideas? Hi adodson See if the article at http://www.techonthenet.com/excel/pivottbls/gtotal_col2007.php does what you want. Regards, Pedro J. > I'm working in Excel 2007 and I can't seem to see my row grand totals in my > pivot table. I can see the grand totals on the columns, but no rows. Any > ideas? Yeah, that is what I would expect it to do too. However, I set that option and don't receive the total. ...

Conditional Formatting
Is it possible to format a portion of a text string within a cell (as opposed to the entire cell). For example, I would like to format the word 'gift' in red font anywhere it a appears in range C2:C417 but only that word, not the entire cell. Not with conditional formatting. But you could change the actual format for that word (or group of characters)... Saved from a previous post (or two!): If you want to change the color of just the characters, you need VBA in all versions. You want a macro???? Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating ...

VBA to add and remove text within cells
Hi, I have a field named "Postal" at the top of column F that always include a number with 5 digits then a city name then a region name, such as "11090 CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this field changed as following : "F-11090", then copying "CARCASSONNE" into the City field which is empty (column G). The city name is always starting just one space character after the postcode, same thing for the region name, it always starts one space character after the city name. The region has to be removed completely. ...

How can you show the new items in a refreshed document?
When refreshing a spreadsheet that is pulling information from a different database, is there a way to highlight the new lines? ...