Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
report - separator lines
I have a list of cities with offices. I want a line to indicate a new city.
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use 100 separator inspite of 1000 separator in excel | Excel Worksheet Functions | |||
Make a report with more lines that you can see on the screen | Excel Discussion (Misc queries) | |||
How can I "autohide" certain lines in a financial report-as Sage | Excel Discussion (Misc queries) | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
separator of thousands | Excel Discussion (Misc queries) |