How to make a thin underline by each group of values? 5

[feipezi]

Hi,

I set up a macro before to add a blank row for each group of values. I now try to do the similar but cannot succeed. I can do it with a 2-step macro or 2 macros. But I'm not satisfied so still trying to do it with one step.

Here is the code that's not working. It works on the first a few rows but fails for the rest.

Any suggestions?

Here is part of the data.
terr family sites
ON1L0102 AK10005 1
ON1C0506 AL11006 1
ON1C0504 AL11040 1
ON1C0506 AL11042 2
ON1C0506 AL11042 2
ON1C0503 AL11043 3
ON1C0503 AL11043 3
ON1C0503 AL11043 3
ON1C0506 AL11044 4
ON1C0504 AL11044 4
ON1C0506 AL11044 4
ON1C0504 AL11044 4
....... .........

Thanks in advance.


Sub AddLinesBasedOnGroup(Col As String) 'nw
Range(Col & 2).Activate
While ActiveCell <> ""
While ActiveCell = ActiveCell.Offset(1)
ActiveCell.Offset(1).Activate
Wend
If ActiveCell <> ActiveCell.Offset(1) Then
ActiveCell.EntireRow.Select
MsgBox ActiveCell.Address
' With Selection.Borders(xlEdgeBottom)
' .LineStyle = xlContinuous
' .ColorIndex = 0
' .TintAndShade = 0
' .Weight = xlThin
' End With
End If
ActiveCell.Offset(1).Activate
Wend
End Sub

Sub tnt()
AddLinesBasedOnGroup "b"
End Sub

 

[SkipVought]

HI,

I would never, never, never (did I say never?) ever put a blank row in a table. NEVER! You destroy the integrity of your table.

BTW, that's most likely why your code is not performing, 'cause you add a row and it looses its place on the sheet.

Rather I'd recommend changing the RowHeight.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[feipezi]

I know you said "NEVER" a few times. But have your client EVER made a request like that. My clients made quite a few requests like that. I'm not supposed to say NEVER to them, right? A macro that adds blank rows in a table based on group of values, which I succeeded in, should be some sort of cosmetics. What's that got to do with table integrity?

Thanks.

 

[SkipVought]

Tables don't have blank rows. Your table has blank rows, so it ends on the first empty row...

While ActiceCell <> ""

So change this...

'
   ActiveCell.Offset([b]2[/b]).Activate
WEnd

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

Skip, thanks for the idea about increasing the row height of the first row of each group. I created a table last week and I wanted each group to stand out. I had changed the forecolor of the text but the row height was much better. I created this procedure in my Personal.XLSB for these occasions. I suppose I could grab the current row height and double it as well as making a couple other changes but this works well.

Sub GroupRowTitle()
[COLOR=#4E9A06]'
' GroupRowTitle Macro
' code to set increase the height of the first row of a grouping in a table
'   expects the grouping column and top row are selected prior to running this procedure
'[/color]
    Dim strValue As Variant
    Dim intRow As Integer
    Dim intCol As Integer
    Dim wks As Worksheet
    intRow = Selection.Row
    intCol = Selection.Column
    Set wks = ActiveWorkbook.ActiveSheet
    strValue = "xxx"
    Do Until strValue = ""
        If strValue <> wks.Cells(intRow, intCol) Then
            wks.Rows(intRow).RowHeight = 30
            strValue = wks.Cells(intRow, intCol)
        End If
        intRow = intRow + 1
    Loop
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Thanks Duane.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Larena]

I know this thread is a couple of weeks old, but I have another possible solution. How about using conditional formatting to apply a bottom border each time the group changes? Assuming that the groups are in column 3 and there is a header, the following formula for a condition on rows 2 and below can be used.

=$C2<>$C3

Once this has been demonstrated, I find most users agree that the blank row isn't necessary after all.

 

[SkipVought]

Larena, you are so right on. When the client says that they need an empty row between groups, a savvy analyst asks some questions to determine the reason for their wanting an empty row. In this instance, it seems that it is for visual emphasis, and there could be several alternatives to inserting blank rows (which in some cases destroys the integrity of formulas within the table as well).

You, Larina, have suggested a solution that might be classified as a corollary to Occam's razor: the simpler solution is the better solution. No VBA to design, no VBA to run, no VBA to maintain: just a simple expression in the Conditional Formatting wizard that will automatically adjust to a Structured Table! That deserves a STAR!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]