Dec 17 2018 08:50 AM
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.
Dec 17 2018 02:50 PM
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.