Insert blank rows when cell value changes 2

[ITALIAORIANA]

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
[pre]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[/pre]

Thanks
Deana

 

[Andrzejek]

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:

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.

 

[ITALIAORIANA]

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

 

[Andrzejek]

>"cherry pick" the stuff I have to put in the actual database
Huge reason to keep the data intact. 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.

 

[ITALIAORIANA]

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

 

[Andrzejek]

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

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.

 

[ITALIAORIANA]

Hahaha you are a riot! Thanks so much for your help.

 

[SkipVought]

Check out faq68-5184. Go Andy!

Also take a look at the AutoFilter feature.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}