Excel add multiple line using sort filter

Question

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.

jwr1138 · Answer

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

City1

City2

City3

to

City1

City2

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.

Forum Discussion

Excel add multiple line using sort filter

Share

Resources