VBA help

[Donai]

Hi, i am after a vba code that will look at column A and put a thick black line accross each data set. I think this is the best way to split the data?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
5	Group	Header2	Header3	Header4	Header5	Header6	Header7	Header8	Header9	Header10	Header11
6	NOMS	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
7	NOMS	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
8	NOMS	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
9	NOMS	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
10	NOMS	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
11	NOMS	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
12	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
13	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
14	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
15	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
16	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
17	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
18	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
19	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
20	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
21	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
22	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
23	LIFE	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
24	CITICASH	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
25	CITICASH	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
26	CITICASH	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
27	CITICASH	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
28	CITICASH	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
29	CITICASH	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
Sheet1

 

[hiker95]

Donai,

LC = Cells(5, Columns.Count).End(xlToLeft).Column

 

[Donai]

Hiker i want to use this code over two worksheets, i tried Sheets(Array, but the code fails.

Sub UnderlineAssets()
    Dim i As Long
    Dim LR As Long
    
    With Sheets(Array("Cash", "Asset"))
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        LC = Cells(5, Columns.Count).End(xlToLeft).Column
        For i = 6 To LR
'            .Range(.Cells(i, "A"), .Cells(i, "U")).Borders(xlEdgeBottom).LineStyle = xlNone
            If .Cells(i, "A") <> .Cells(i - 1, "A") Then .Range(.Cells(i - 1, "A"), .Cells(i - 1, LC)).Borders(xlEdgeBottom).Weight = xlMedium
        Next i
    End With
End Sub

 

[hiker95]

Donai,

The following codes seems to work correctly with your posted screenshot.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub UnderlineAssets()
Dim i As Long
Dim LR As Long, LC As Long
Dim ws As Worksheet
For Each ws In Worksheets(Array("Cash", "Asset"))
  With ws
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    LC = .Cells(5, Columns.Count).End(xlToLeft).Column
    For i = 6 To LR
'            .Range(.Cells(i, "A"), .Cells(i, "U")).Borders(xlEdgeBottom).LineStyle = xlNone
      If .Cells(i, "A") <> .Cells(i - 1, "A") Then .Range(.Cells(i - 1, "A"), .Cells(i - 1, LC)).Borders(xlEdgeBottom).Weight = xlMedium
    Next i
  End With
Next ws
End Sub