Home

Excel add multiple line using sort filter

%3CLINGO-SUB%20id%3D%22lingo-sub-302854%22%20slang%3D%22en-US%22%3EExcel%20add%20multiple%20line%20using%20sort%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-302854%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20one%20big%20file%20contain%20couple%20columns%20like%20Country%2C%20State%2C%20City%2C%20Zip%20Code.%20I%20want%20to%20add%20a%20line%20after%20each%20city.%20I%20have%20tried%20at%20my%20level%20best%20but%20couldn't%20find%20the%20solution.%20I%20have%20already%20sorted%20by%20Country%2C%20State%2C%20City%20and%20Zip%20code.%20Now%20my%20city%20name%20list%20is%20A%20to%20Z%20for%20each%20state%20and%20country.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-302854%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303087%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20add%20multiple%20line%20using%20sort%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303087%22%20slang%3D%22en-US%22%3E%3CP%3EHI%2C%20You%20just%20want%20to%20insert%20a%20row%20in%20between%20each%20city%20then%3F%20Ie.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECity1%3C%2FP%3E%3CP%3ECity1%3C%2FP%3E%3CP%3ECity2%3C%2FP%3E%3CP%3ECity2%3C%2FP%3E%3CP%3ECity3%3C%2FP%3E%3CP%3ECity3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECity1%3C%2FP%3E%3CP%3ECity1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECity2%3C%2FP%3E%3CP%3ECity2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECity3%3C%2FP%3E%3CP%3ECity3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20that%20is%20correct%2C%20here%20is%20a%20VBA%20option%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20AddRows()%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20FirstDataRow%20As%20Integer%3CBR%20%2F%3EDim%20LastDataRow%20As%20Integer%3CBR%20%2F%3EDim%20CityColumn%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3EFirstDataRow%20%3D%202%3CBR%20%2F%3ELastDataRow%20%3D%2013%3CBR%20%2F%3ECityColumn%20%3D%209%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20i%20As%20Integer%3CBR%20%2F%3EDim%20Cell%20As%20Range%3CBR%20%2F%3EDim%20CityName%20As%20String%3CBR%20%2F%3EDim%20CityName2%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20i%20%3D%20LastDataRow%20To%20FirstDataRow%20Step%20-1%3CBR%20%2F%3E%3CBR%20%2F%3ECityName%20%3D%20Cells(i%2C%20CityColumn).Value%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Not%20CityName%20%3D%20CityName2%20Then%3CBR%20%2F%3E%20Rows(i%20%2B%201%20%26amp%3B%20%22%3A%22%20%26amp%3B%20i%20%2B%201).Insert%20Shift%3A%3DxlDown%2C%20CopyOrigin%3A%3DxlFormatFromLeftOrAbove%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ECityName2%20%3D%20CityName%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20i%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3EJust%20need%20to%20change%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EFirstDataRow%20%3D%202%3CBR%20%2F%3ELastDataRow%20%3D%2013%3CBR%20%2F%3ECityColumn%20%3D%209%3C%2FPRE%3E%3CP%3ETo%20the%20appropriate%20row%2Fcolumn%20numbers%20for%20your%20worksheet%20and%20run%20the%20macro.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Kartikey5
Occasional Visitor

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. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies