Blanks in Column

[GelukC]

Hello,

I have a question:

I have a sheet with companies, names and some other data. Now I want to sort the data on the companies, but when the next company is in the next row I want a blank row between them.

this is the sheet now :

UPC John Smith
UPC Jane Do
Ziggo Jack Jones
XS4All Lara Croft

And I want It to be like this :

UPC John Smith
UPC Jane Do

Ziggo Jack Jones

XS4All Lara Croft

Can someone please tell me how to do this in Excel VBA

 

[Weaver]

Assumes your company names are in col A

Sub insertblankrow()
    Application.ScreenUpdating = 0
    fr = 1 'set this to the row number of your headers
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lr To fr + 2 Step -1
        If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then c.EntireRow.Insert shift:=xlDown
    Next
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

In any cell, how can we tell where the company name ends and the 'other data' starts?

Is the company name always just a single word?

Or is the 'other data' always two words?

 

[GelukC]

There seems to be an error in this line :

If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then c.EntireRow.Insert shift:=xlDown

EDIT:

I see the past post now.

Column A : Company Name
Column B : Person
Column C : City
etc.

The data is insert through a userform, but I don't think that makes a difference to the coding I think...

I have no headers. A1 is the first datacell

 

[GelukC]

There seems to be an error in this line :

If Cells(i, 1).Value <> Cells(i - 1, 1).Value Then c.EntireRow.Insert shift:=xlDown

EDIT:

I see the past post now.

Column A : Company Name (can be more then one word)
Column B : Person (can be more then one word)
Column C : City (can be more then one word)
etc.

The data is insert through a userform, but I don't think that makes a difference to the coding I think...

I have no headers. A1 is the first datacell

A1 UPC - B1 John Smith - C1 New York
A2 UPC - B2 Jane Do - C2 New York
A3 Ziggo NL - B3 Lara Croft - C3 Boston
A4 KPN = B4 Jopie Seller - C4 Boston

And is must be :

A1 UPC - B1 John Smith - C1 New York
A2 UPC - B2 Jane Do - C2 New York
A3 B3 C3
A4 Ziggo NL - B4 Lara Croft - C4 Boston
A5 B5 C5
A6 KPN = B6 Jopie Seller - C6 Boston

I hope it's clear now...

 

[Peter_SSs]

OK, it wasn't clear in the first post that the company name and the other data were in different columns. Weaver's code is close but he didn't define what 'c' was. Try this version in a copy of your workbook.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> InsertBlankRows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Cells(Rows.Count, "A").End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> i = LR <SPAN style="color:#00007F">To</SPAN> 2 <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">If</SPAN> Cells(i, 1).Value <> Cells(i - 1, 1).Value <SPAN style="color:#00007F">Then</SPAN> Rows(i).Insert<br>    <SPAN style="color:#00007F">Next</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[GelukC]

 

[Peter_SSs]

With my previous code, add the blue line where shown

Application.ScreenUpdating = False
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
LR = Cells(Rows.Count, "A").End(xlUp).Row