Excel add multiple line using sort filter

Copper Contributor

I have one big file contain couple columns like Country, State, City, Zip Code. I want to add a line after each city. I have tried at my level best but couldn't find the solution. I have already sorted by Country, State, City and Zip code. Now my city name list is A to Z for each state and country.

1 Reply

HI, You just want to insert a row in between each city then? Ie.

 

City1

City1

City2

City2

City3

City3

 

to 

 

City1

City1

 

City2

City2

 

City3

City3

 

Assuming that is correct, here is a VBA option:

 

Sub AddRows()

Dim FirstDataRow As Integer
Dim LastDataRow As Integer
Dim CityColumn As Integer

FirstDataRow = 2
LastDataRow = 13
CityColumn = 9

Dim i As Integer
Dim Cell As Range
Dim CityName As String
Dim CityName2 As String


For i = LastDataRow To FirstDataRow Step -1

CityName = Cells(i, CityColumn).Value

If Not CityName = CityName2 Then
Rows(i + 1 & ":" & i + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If

CityName2 = CityName

Next i

End Sub

Just need to change 

 

FirstDataRow = 2
LastDataRow = 13
CityColumn = 9

To the appropriate row/column numbers for your worksheet and run the macro.