INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Insert blank rows when cell value changes

Insert blank rows when cell value changes

(OP)
Hi, I found the code at the bottom on this site and I have been trying to modify and have been unsuccessful. I have thousands of rows of data and I need to break the data out by adding a blank row based on values in column 2. Any help would be greatly appreciated.

When the cell value changes in column 2 from 1 to 2, I want to insert one blank row after the end of the first value. When it changes from 2 to 3, I want to insert one blank row etc... The numbers will not necessarily be consecutive but they are grouped together.

My current data:
Column 1, Column 2
A435, 1
B792, 1
C456, 1
DD58, 1
F556, 2
G887, 2
HAA, 2
J5684, 25
LADF, 30
NAD6F4, 790
KSF, 1000
L65, 1000



I want it to be:
A435, 1
B792, 1
C456, 1
DD58, 1

F556, 2
G887, 2
HAA, 2

J5684, 25

LADF, 30

NAD6F4, 790

KSF, 1000
L65, 1000



The code below works but adds a blank row in between every row. I
Dim rval As String, i As Long
rval = Cells(1, 2): i = 2
Do Until Trim(Cells(i, 1) & "") = ""
  If Cells(i, 1) <> rval Then
    rval = Cells(i, 1)
    Rows(i).Insert shift:=xlDown
    i = i + 1
  End If
  i = i + 1
Loop 

Thanks
Deana

RE: Insert blank rows when cell value changes

When you insert a blank row in-between rows, you loose the ability to work with the data. What is the reason for the blank rows? If only for display purposes, to easily see where values change, wouldn't be better to increase the row's height? Or set bottom/top border of the cell(s)?

If you are really set on inserting a blank row, try this code:

CODE

Dim rval As String, i As Long
rval = Cells(2, 2): i = 2
Do Until Trim(Cells(i, 2) & "") = ""
  If Cells(i, 2) <> rval Then
    rval = Cells(i, 2)
    Rows(i).Insert shift:=xlDown
    i = i + 1
  End If
  i = i + 1
Loop 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Insert blank rows when cell value changes

(OP)
Hi Andrzejek,

I need to add the rows so it's easier for me to "cherry pick" the stuff I have to put in the actual database after an additional formula is added. A manual addition of the blank rows is not possible since the file length varies and the one I am working with right now has 11,133 rows.

Thanks
Deana

RE: Insert blank rows when cell value changes

>"cherry pick" the stuff I have to put in the actual database
Huge reason to keep the data intact. smile After you mark what you want to put in the data base, you wouldn't need to by-pass empty rows.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Insert blank rows when cell value changes

(OP)
Hi Andrzejek,

If I could give you a million stars for this, I would!!! This is exactly what I needed and it works perfectly.

I just inherited this process a few days ago and it is a bit convoluted right now. However, both the quarter 2 and 3 updates are past due so I needed to do it the way I was shown until I get a handle on this. I just REALLY didn't want to take all these files and manually add the blank row like I was shown.

Thanks
Deana

RE: Insert blank rows when cell value changes

I accept cash, personal checks, and all major credit cards smile

No, you do not want to do this 'by hand' for 11,133 rows, you would go crazy. Computers are a lot better at it than us, people Plus - it is Friday!

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Insert blank rows when cell value changes

(OP)
Hahaha you are a riot! Thanks so much for your help. smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close