Code To Insert Blank Rows

I have a file with a listing of entries, and in column B of all the entries, 
are unique names. I need to create the code to reference the listing, which 
at times will be 200 entries and other times 1000 entries, and evaluate the 
data in column B, to then insert 2 blank rows every time a different name 
appears. For instance

Initially column B has
(Row 1) Pears
(2) Pears
(3) Pears
(4) Apples
(5) Grapes
(6) Grapes

After the desired code, the results would be..
(Row 1) Pears
(2) Pears
(3) Pears
(4) Blank Row
(5) Blank Row
(6) Apples
(7) Blank Row
(8) Blank Row
(9) Grapes
(10) Grapes
END OF FILE

Any and all help will be appreciated - Thank You In Advance
0
Utf
3/16/2010 11:53:01 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
980 Views

Similar Articles

[PageSpeed] 17

Try this which will ignore previously inserted blank rows:

Sub InsertRows()
Dim LastRow As Long
Dim CheckValue As String

LastRow = Range("B" & Rows.Count).End(xlUp).Row
CheckValue = Range("B" & LastRow).Value

For r = LastRow To 1 Step -1
    If CheckValue <> Range("B" & r) And Range("B" & r) <> "" Then
        CheckValue = Range("B" & r).Value
        Range("B" & r + 1).Resize(2).EntireRow.Insert
    End If
Next
End Sub


Regards,
Per

"MWS-C360" <MWSC360@discussions.microsoft.com> skrev i meddelelsen 
news:8A8B7C6C-0D03-4A7C-A0A0-078E35C1598B@microsoft.com...
> I have a file with a listing of entries, and in column B of all the 
> entries,
> are unique names. I need to create the code to reference the listing, 
> which
> at times will be 200 entries and other times 1000 entries, and evaluate 
> the
> data in column B, to then insert 2 blank rows every time a different name
> appears. For instance
>
> Initially column B has
> (Row 1) Pears
> (2) Pears
> (3) Pears
> (4) Apples
> (5) Grapes
> (6) Grapes
>
> After the desired code, the results would be..
> (Row 1) Pears
> (2) Pears
> (3) Pears
> (4) Blank Row
> (5) Blank Row
> (6) Apples
> (7) Blank Row
> (8) Blank Row
> (9) Grapes
> (10) Grapes
> END OF FILE
>
> Any and all help will be appreciated - Thank You In Advance 

0
Per
3/16/2010 12:06:40 PM
Try the below macro

Sub Macro()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Next
End Sub

-- 
Jacob


"MWS-C360" wrote:

> I have a file with a listing of entries, and in column B of all the entries, 
> are unique names. I need to create the code to reference the listing, which 
> at times will be 200 entries and other times 1000 entries, and evaluate the 
> data in column B, to then insert 2 blank rows every time a different name 
> appears. For instance
> 
> Initially column B has
> (Row 1) Pears
> (2) Pears
> (3) Pears
> (4) Apples
> (5) Grapes
> (6) Grapes
> 
> After the desired code, the results would be..
> (Row 1) Pears
> (2) Pears
> (3) Pears
> (4) Blank Row
> (5) Blank Row
> (6) Apples
> (7) Blank Row
> (8) Blank Row
> (9) Grapes
> (10) Grapes
> END OF FILE
> 
> Any and all help will be appreciated - Thank You In Advance
0
Utf
3/16/2010 12:12:05 PM
This worked perfectly!!!!

Thank You - I really appreciate your assistance!!!!

"Jacob Skaria" wrote:

> Try the below macro
> 
> Sub Macro()
> Dim lngRow As Long
> For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> Rows(lngRow).EntireRow.Resize(2).Insert
> End If
> Next
> End Sub
> 
> -- 
> Jacob
> 
> 
> "MWS-C360" wrote:
> 
> > I have a file with a listing of entries, and in column B of all the entries, 
> > are unique names. I need to create the code to reference the listing, which 
> > at times will be 200 entries and other times 1000 entries, and evaluate the 
> > data in column B, to then insert 2 blank rows every time a different name 
> > appears. For instance
> > 
> > Initially column B has
> > (Row 1) Pears
> > (2) Pears
> > (3) Pears
> > (4) Apples
> > (5) Grapes
> > (6) Grapes
> > 
> > After the desired code, the results would be..
> > (Row 1) Pears
> > (2) Pears
> > (3) Pears
> > (4) Blank Row
> > (5) Blank Row
> > (6) Apples
> > (7) Blank Row
> > (8) Blank Row
> > (9) Grapes
> > (10) Grapes
> > END OF FILE
> > 
> > Any and all help will be appreciated - Thank You In Advance
0
Utf
3/16/2010 12:33:02 PM
> Rows(lngRow).EntireRow.Resize(2).Insert

Since you used the Rows object in the above line from your posted code, you 
don't need to specify the EntireRow property... this should work the same 
way...

Rows(lngRow).Resize(2).Insert

-- 
Rick (MVP - Excel)


"Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message 
news:D0AFBB5D-D846-4D5A-9B1A-464A23D6C010@microsoft.com...
> Try the below macro
>
> Sub Macro()
> Dim lngRow As Long
> For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
> If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> Rows(lngRow).EntireRow.Resize(2).Insert
> End If
> Next
> End Sub
>
> -- 
> Jacob
>
>
> "MWS-C360" wrote:
>
>> I have a file with a listing of entries, and in column B of all the 
>> entries,
>> are unique names. I need to create the code to reference the listing, 
>> which
>> at times will be 200 entries and other times 1000 entries, and evaluate 
>> the
>> data in column B, to then insert 2 blank rows every time a different name
>> appears. For instance
>>
>> Initially column B has
>> (Row 1) Pears
>> (2) Pears
>> (3) Pears
>> (4) Apples
>> (5) Grapes
>> (6) Grapes
>>
>> After the desired code, the results would be..
>> (Row 1) Pears
>> (2) Pears
>> (3) Pears
>> (4) Blank Row
>> (5) Blank Row
>> (6) Apples
>> (7) Blank Row
>> (8) Blank Row
>> (9) Grapes
>> (10) Grapes
>> END OF FILE
>>
>> Any and all help will be appreciated - Thank You In Advance 

0
Rick
3/16/2010 12:42:08 PM
>        Range("B" & r + 1).Resize(2).EntireRow.Insert

For the above line from your posted code, you could use Rows instead of 
Range and do away with the call to the EntireRow property...

Rows(r + 1).Resize(2).Insert

-- 
Rick (MVP - Excel)


"Per Jessen" <per.jessen@mail.dk> wrote in message 
news:O$svjEQxKHA.3896@TK2MSFTNGP02.phx.gbl...
> Try this which will ignore previously inserted blank rows:
>
> Sub InsertRows()
> Dim LastRow As Long
> Dim CheckValue As String
>
> LastRow = Range("B" & Rows.Count).End(xlUp).Row
> CheckValue = Range("B" & LastRow).Value
>
> For r = LastRow To 1 Step -1
>    If CheckValue <> Range("B" & r) And Range("B" & r) <> "" Then
>        CheckValue = Range("B" & r).Value
>        Range("B" & r + 1).Resize(2).EntireRow.Insert
>    End If
> Next
> End Sub
>
>
> Regards,
> Per
>
> "MWS-C360" <MWSC360@discussions.microsoft.com> skrev i meddelelsen 
> news:8A8B7C6C-0D03-4A7C-A0A0-078E35C1598B@microsoft.com...
>> I have a file with a listing of entries, and in column B of all the 
>> entries,
>> are unique names. I need to create the code to reference the listing, 
>> which
>> at times will be 200 entries and other times 1000 entries, and evaluate 
>> the
>> data in column B, to then insert 2 blank rows every time a different name
>> appears. For instance
>>
>> Initially column B has
>> (Row 1) Pears
>> (2) Pears
>> (3) Pears
>> (4) Apples
>> (5) Grapes
>> (6) Grapes
>>
>> After the desired code, the results would be..
>> (Row 1) Pears
>> (2) Pears
>> (3) Pears
>> (4) Blank Row
>> (5) Blank Row
>> (6) Apples
>> (7) Blank Row
>> (8) Blank Row
>> (9) Grapes
>> (10) Grapes
>> END OF FILE
>>
>> Any and all help will be appreciated - Thank You In Advance
> 

0
Rick
3/16/2010 12:44:41 PM
Yes Rick. I initially wrote that as Range() and later changed that to 
Rows()...missed to remove that. Thanks for pointing that out..

-- 
Jacob


"Rick Rothstein" wrote:

> > Rows(lngRow).EntireRow.Resize(2).Insert
> 
> Since you used the Rows object in the above line from your posted code, you 
> don't need to specify the EntireRow property... this should work the same 
> way...
> 
> Rows(lngRow).Resize(2).Insert
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message 
> news:D0AFBB5D-D846-4D5A-9B1A-464A23D6C010@microsoft.com...
> > Try the below macro
> >
> > Sub Macro()
> > Dim lngRow As Long
> > For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
> > If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
> > Rows(lngRow).EntireRow.Resize(2).Insert
> > End If
> > Next
> > End Sub
> >
> > -- 
> > Jacob
> >
> >
> > "MWS-C360" wrote:
> >
> >> I have a file with a listing of entries, and in column B of all the 
> >> entries,
> >> are unique names. I need to create the code to reference the listing, 
> >> which
> >> at times will be 200 entries and other times 1000 entries, and evaluate 
> >> the
> >> data in column B, to then insert 2 blank rows every time a different name
> >> appears. For instance
> >>
> >> Initially column B has
> >> (Row 1) Pears
> >> (2) Pears
> >> (3) Pears
> >> (4) Apples
> >> (5) Grapes
> >> (6) Grapes
> >>
> >> After the desired code, the results would be..
> >> (Row 1) Pears
> >> (2) Pears
> >> (3) Pears
> >> (4) Blank Row
> >> (5) Blank Row
> >> (6) Apples
> >> (7) Blank Row
> >> (8) Blank Row
> >> (9) Grapes
> >> (10) Grapes
> >> END OF FILE
> >>
> >> Any and all help will be appreciated - Thank You In Advance 
> 
> .
> 
0
Utf
3/16/2010 1:23:01 PM
Reply:

Similar Artilces:

How do I insert hypens in existing column representing social sec.
I need to insert two hyphens in an existing column of numbers (represents social security numbers) in Excel, i.e. xxx-xx-xxxx. Is there a formual that will accomplish this? Yes, in US excel there is a ssn format under format>cells>number>special it looks like 000-00-0000 that assumes that your data are numbers and not text Regards, Peo Sjoblom "hard head" wrote: > I need to insert two hyphens in an existing column of numbers (represents > social security numbers) in Excel, i.e. xxx-xx-xxxx. Is there a formual that > will accomplish this? ...

Avoiding Blanks In SUMPRODUCT
With data: Area of 0 to 500 Area of 501 to 1000 Area of 1001 to 1500 Area of 1501 to 2000 Area of 2001 to 2500 Area of 2501 to 3000 In A1:A6 I use the formula: =SUMPRODUCT(--VALUE(MID(A1:A6,FIND("to",A1:A6)+2,LEN(A1:A6)-FIND("to",A1:A6)))) to sum up the MAXIMUM Limit in entries resulting to: 10,500 However, how to have the same formula be applied and working correctly if there is a blank cell in A1:A6? -- Thanx in advance, Best Regards, Faraz Hi Faraz Check out the below... =SUMPRODUCT(--RIGHT(SUBSTITUTE("0" & A1:A6,&quo...

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

insert metadata
I am trying to find out how to include the Excel metadata into a worksheet. In Word, it would be called a field and can be inserted by clicking Insert, Field, Document Information, DocProperty and then selecting one of the various properties including any custom fields. I am unable to find the equivalent in Excel. Thanks, Mike Excel doesn't support this directly. But you could use a userdefined function to retrieve either builtindocumentproperties or even CustomDocumentProperties. Here's one by Chip Pearson that does both: http://google.com/groups?threadm=e9D1rKor9GA.202%...

Can youp propagate changes to inserted text from file?
If you insert "text from file" in a document, but later make changes to the source file, can the changes to the source file be propagated to the document where you inserted the text? If you insert a link to the source file. Otherwise no. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>>...

more than 65, 500 rows
Is there a product that allows more than 65k rows? ...

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

+ on top of columns & next to rows
I have a spreadsheet that has + on top of the columns and next to the rows. when you click on the + it hides or unhides the row/columns. there are also numbers at the very top left hand corner that when clicked on hides or unhided all of the corresponding rows/columns. I would like to do this to other spreadsheets. Can someone please let me know how this is done? Thanks in advance. Look up "Outline" in the HELP files. It's the same format as when you use subtotals. -- HTH, RD ============================================== Please keep all correspondence within the G...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

row-based Bar Chart: How to make bars colorful?
I have a Bar Chart based on a single row. Each bar represents a value in a cell in that row. When I mouseover a bar they all show same series name (row) but different points (columns). All bars are same color. How do I make colors different when it is all one series? When I change color for that series, all bars switch to selected color... Thank you Hi, Two ways. Change the series to be plotted by Columns. Right click chart and pick Source Data. On the Data Range tab select Series In: Columns. This will make each data point a series. The other way is the select the series and on the...

Change code with code?
Hi All..... I've got 31 Excel .xlsm files to modify the "Change Event" macro in. Is it possible to open and perform this with code, or must I do each one by hand? TIA Vaya con Dios, Chuck, CABGx3 If it is the same change in each sheet, it would probably be just as easy to go into the VBE, double click on the first sheet, make the correction, then copy that correction and double click the second sheet, delete and paste. Repeat the process 30 times. Takes less time than writing the code to do it. "CLR" <CLR@discussions.microsoft.com> wr...

Inserting comments on graphs
How do I insert a comment on a graph. I want to call out something on the graph. I have tried drawing an arrow and adding text by the arrow but it is not part of the graph and is sent behind the graph when the graph is selected. Thanks. Chris Hi AFAIK this is not possible -- Regards Frank Kabel Frankfurt, Germany "Christopher Anderson" <ChristopherAnderson@discussions.microsoft.com> schrieb im Newsbeitrag news:2DF90C73-F26B-47CB-BFF4-CF2408B0683E@microsoft.com... > How do I insert a comment on a graph. I want to call out something on the > graph. I have tried...

vlookup is reporting 0 when I want it to show a blank cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, <br><br>My vlookup formula is giving 0s when it encounters cells I've left blank my table. I'd like to know how I can get it to provide blank cells when it encounters blank cells. <br><br>Here's my formula string: <br><br>=VLOOKUP($C506,'Threats &amp; Conservation Actions'!$B$3:$N$42,6,0) <br><br>Thanks! The simple way might be to set the Sheet not to show 0 values. See the Excel help topic "Control how zero values are displaye...

Access 2003
I have a form that goes to a subform for searching. It works perfectly on my computer; however, when another user opens it and attempts to perform a search, they receive the Error Code 2455 message. When I debug, it gets stuck on "Me.book_subform.Form.Filter = strWhereSearch" and says that book_subform cannot be found. It isn't a rights issue, it isn't a mapping issue. What am I missing? check your naming. This is an issue that often confused. In this syntax: Me.book_subform.Form.Filter > = strWhereSearch book_subform is the name of the subform control on the main ...

Recording macros with relative row and column relationships
I am trying to copy information in Excel 2003 from one row to a new row on separate worksheets, using a macro. However, the macro records actual rows and columns (even though the "$" is not present), which I have verified by running the macro with a newly created row. (It still copies the previous row to the other worksheets and not the new row.) How do I record a macro with relative rows/columns, so that I can copy the data from each newly created row on the first worksheet to the remaining 14 worksheets in the workbook? (Please note that I am not copying all the data fro...

inserting a number of rows with formula's
I want to make the following procedure automatically by just entering a number (X) in a cell: - inserting a number (X) of rows between a range - select the two rows above, which have formula’s and numbers - ‘pulling down’ those two rows over the number of inserted rows (X) to give them the same formula’s and (following) numbers If I change the number, the inserted rows must be removed or added. Can this be done? The following has most of what you want Insert Rows and Maintain Formulas http://www.mvps.org/dmcritchie/excel/insrtrow.htm You could use a negative number for deletion a...

Write a code by code
How can I write a code from procedure to some Workbook's ThisWorkbook? With procedure1 a make Workbook with a table. I need to write Auto_Close procedure to ThisWorkbook which makes some controls before I close it. Is it possible? <jenista.j@discussions.microsoft.com> wrote in message news:0be501c52fab$ba0b6830$a601280a@phx.gbl... > How can I write a code from procedure to some Workbook's > ThisWorkbook? With procedure1 a make Workbook with a table. > I need to write Auto_Close procedure to ThisWorkbook which > makes some controls before I close it. Is it possible? ...

Coding Duplicate Records
How can I code that a record is to be "K" kept or "D" deleted when compared to its next row? Example: A sorted file by Address BEFORE LastName Address Sales Date Keep/Delete 1. WASSEM 0N642 TITUS PL 67,500 6/25/2001 2. HART 0N655 E WEAVER CIR 70,000 3/19/2001 3. HART 0N655 E WEAVER CIR 40,000 3/19/2001 4. HART 0N655 WEAVER CIRCLE 33,700 6/19/1998 5. POYTH 0N662 W WEAVER CIR 294,690 6/21/2000 6. BLAND 0N670 GREEN PL ...