how to underline rows in filtered list 1

[matttclark]

Hello,

I am a VBA n00b woudl appreciate any help. In excel 2003 the team has a list that is used to track planned expenses for the entire year. Each row is a record of one planned expense request, ie. Travel, equipment, supplies, etc.

The list starts in row 3 and data is in columns (A-AB) where the first four columns are

A=Dept B=Fiscal Qtr C=Month D= requestors name

Weekly, a report is created from the list by autofiltering the list on column C (month). Then a macro is run to sort the data and hide certain columns and rows 1&2 which are notes rows.

The first sort criteria when the report is created is on column A (Dept) in ascending order. The values in column A are all numerical, i.e 2400, 2410, 2420, etc.

Now the boss wants to call out on her report where the Dept (column A) values change in the filtered list to make it easier for her to scan the report list.

Presently this is done by manually underlining the last row showing for each team, ie. If there are 10 entries for team 2400, then the last row of team 2400 is manually underlined, then if there are 8 entries for team 2410, the 8th entry in underlined, etc.

So I have cobbled together some code from different sources and made soem changes on my own to try to automagically underline the last visible row in the filtered list for each Dept. However it reads the whole list and not the filtered values. Is there a way to get this to work on a filtered list.

Here is the code I am using:

    Dim intRowval As Integer, intLastRowval As Integer
    Dim ws As Worksheet
 
'   Get the row number of the last row of data
    intLastRowval = Cells(Rows.Count, "A").End(xlUp).Row
'   Row 3 is column headings, so start on the 4th row of data
    For intRowval = 4 To intLastRowval
 
'       If the value in column A (=1) has changed from the previous row value
        If Cells(intRowval, 1) <> Cells(intRowval - 1, 1) Then
 
'           Choose which cells to underline, i.e. from column 1 to 14
            With Range(Cells(intRowval - 1, 1), Cells(intRowval - 1, 14)).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThick
                .ColorIndex = vbBlack
            End With
        End If
    Next intRowval
End Sub

 

[SkipVought]

Hi,

I'd use Conditional Formatting. I assume that one column has your DEPT values, repeated on each row. The break occurs when the DEPT value changes. Assuming that it's column A and assuming a row of headings in row 1, then the formula in CF would be...
[tt]
=A2<>A1
[/tt]
No VBA required!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[matttclark]

Skip,

Thanks for the feedback and yes that would work except for a couple gotchas that made me go for the vba solution.

1) They only want the underline to apply when the analyst runs this report. The rest of the time the way they filter and look at the list with other reports it would be a distraction. I have a "restore" macro that returns this to a "default" setting from which the other reports are run.

2) They do some other manaul formatting (colors, etc) that would be lost if I copy/pasted formats for conditional formatting

3) They want a heavy undeline and at least in excel 2003 that is not an option in conditional formatting (unless i missed something).

Any thoughts on a VBA solution, thanks!

 

[xlbo]

For each c in range(A4:A" & intLastRowval).SpecialCells(xlCellTypeVisible)    
 If c <> c.offset(1,0) Then
 
'Choose which cells to underline, i.e. from column 1 to 14
    With c.resize(1,14).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThick
            .ColorIndex = vbBlack
    End With
 End If
Next c

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question