Solved

Insert blank row between different numbers.

Posted on 2014-04-08
8
523 Views
Last Modified: 2014-04-13
Good Day Experts,

I have a rather large excel spreadsheet in which column A has sorted numbers in with a single number 4, 10, 20 or more numbers that may be the same.  I would like if possible to have a macro that I would run that would insert one or two rows in the entire row to separate the different number or groups of like numbers throughout the column.

Thanks for your assistance.

I have attached a spreadsheet for your review.
0
Comment
Question by:Beeyen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 36

Expert Comment

by:Kimputer
ID: 39987513
Can't find the attachment
0
 
LVL 36

Expert Comment

by:Kimputer
ID: 39987538
For now, my code looks like this:

Sub test()

'get row count
RowCount = ActiveSheet.UsedRange.Rows.Count

i = 1
Do While i < RowCount
    'get row and next row
    If Cells(i, 1).Value <> Cells(i + 1, 1).Value Then
		'insert row
        Cells(i + 1, 1).Select
        ActiveCell.EntireRow.Select
        Selection.Insert Shift:=xlDown
        'adjust i because of added row
		i = i + 1
    End If
    
    i = i + 1

Loop



End Sub

Open in new window

0
 

Author Comment

by:Beeyen
ID: 39988946
I have attached the spreadsheet.
Expert-workbook.xlsx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 36

Expert Comment

by:Kimputer
ID: 39988960
Because the third column is Yellow, I assume I have to check that. Therefore the code is only slightly different:

Sub test()

'get row count
RowCount = ActiveSheet.UsedRange.Rows.Count

i = 1
Do While i < RowCount
    'get row and next row
    If Cells(i, 3).Value <> Cells(i + 1, 3).Value Then
        'insert row
        Cells(i + 1, 3).Select
        ActiveCell.EntireRow.Select
        Selection.Insert Shift:=xlDown
        'adjust i because of added row
        i = i + 1
    End If
    
    i = i + 1

Loop



End Sub

Open in new window

0
 

Author Comment

by:Beeyen
ID: 39988981
I tryed the coding you provided. It works but it inserts a row everyother number as oppose to grouping the like numbers then adding a row. I have attached the workbook for your use.  let me know
thanks
0
 

Author Comment

by:Beeyen
ID: 39989556
Okay! The coding worked down to a certain number of cells then stopped. I attached the worksheet and highlighted at the gray where the macro stopped.  Let me know

Thanks
Expert-workbook.xlsm
0
 
LVL 36

Accepted Solution

by:
Kimputer earned 500 total points
ID: 39989736
bug in code:

Sub test()

'get row count
RowCount = ActiveSheet.UsedRange.Rows.Count

i = 1
Do While i < RowCount
    'get row and next row
    If Cells(i, 3).Value <> Cells(i + 1, 3).Value Then
        'insert row
        Cells(i + 1, 3).Select
        ActiveCell.EntireRow.Select
        Selection.Insert Shift:=xlDown
        'adjust i because of added row
        i = i + 1
RowCount = RowCount + 1

    End If
    
    i = i + 1

Loop



End Sub

Open in new window

0
 

Author Closing Comment

by:Beeyen
ID: 39997732
Thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question