report - separator lines
 

I have a list of cities with offices. I want a line to indicate a new city.

report - separator lines
 

Give this macro a try...

Sub LineAtCityChange()
Dim X As Long
Dim LastRow As Long
Const DataStartRow As Long = 2
Const CityColumn As String = "A"
Const TotalColumns = 3
With Worksheets("Sheet2")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Columns(CityColumn).Resize(, TotalColumns).Borders. _
LineStyle = xlLineStyleNone
For X = DataStartRow To LastRow
If Cells(X, CityColumn).Value < Cells(X + 1, CityColumn).Value Then
Cells(X, CityColumn).Resize(, TotalColumns). _
Borders(xlEdgeBottom). _
LineStyle = xlContinuous
End If
Next
End With
End Sub

You will have to change the worksheet reference in the With statement from
"Sheet2" to your own sheet name and also you have to set the three Const
statements which establish the row your first city is listed on, the column
where your cities are listed and the total number of column to be underlined
(starting with the city's column).

--
Rick (MVP - Excel)


"Philippe C." wrote in message
...
I have a list of cities with offices. I want a line to indicate a new
city.

report - separator lines
 

Thanks from Antwerpen. It works.
I had thought in the direction of pivot tables.

Often in Visio I have to answer "Visio is not a drawing program"
I think Excel sadly is not a reporting tool.
I have worked in Crystal Reports yet.


Maybe I should use Access with Excel as an ODBC connection.



"Rick Rothstein" wrote:

Give this macro a try...

Sub LineAtCityChange()
Dim X As Long
Dim LastRow As Long
Const DataStartRow As Long = 2
Const CityColumn As String = "A"
Const TotalColumns = 3
With Worksheets("Sheet2")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Columns(CityColumn).Resize(, TotalColumns).Borders. _
LineStyle = xlLineStyleNone
For X = DataStartRow To LastRow
If Cells(X, CityColumn).Value < Cells(X + 1, CityColumn).Value Then
Cells(X, CityColumn).Resize(, TotalColumns). _
Borders(xlEdgeBottom). _
LineStyle = xlContinuous
End If
Next
End With
End Sub

You will have to change the worksheet reference in the With statement from
"Sheet2" to your own sheet name and also you have to set the three Const
statements which establish the row your first city is listed on, the column
where your cities are listed and the total number of column to be underlined
(starting with the city's column).

--
Rick (MVP - Excel)


"Philippe C." wrote in message
...
I have a list of cities with offices. I want a line to indicate a new
city.