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

 

[Donai]

*bump*

 

[jmthompson]

not sure what exactly you are looking to do? do you simply want to put a border around each group of values in column A to visually separate them or something more complex?

 

[Donai]

not sure what exactly you are looking to do? do you simply want to put a border around each group of values in column A to visually separate them or something more complex?

Correct, I want 2 visually separate each data set

 

[328mike]

1. Select cell A1
2. Hold Control + Shift and press right arrow
3. Hold Control + Shift and press down arrow
4. Right click cells and go to format cells
5. Select border tab

 

[rpaulson]

how about this.

Sub Underline()
 'Add Border
For r = 7 To 29
Range(Cells(r, "A"), Cells(r, "K")).Borders(xlEdgeBottom).LineStyle = xlNone
If Cells(r, "A") <> Cells(r - 1, "A") Then Range(Cells(r - 1, "A"), Cells(r - 1, "K")).Borders(xlEdgeBottom).Weight = xlMedium
Next r

End Sub

Ross

 

[Donai]

Thanks, i cleaned it up a bit.

Sub Underline()

    Dim i As Long
    Dim LR As Long
    
    With Sheets(1)
        LR = Range("A" & Rows.Count).End(xlUp).Row
        For i = 6 To LR
            .Range(.Cells(i, "A"), .Cells(i, "K")).Borders(xlEdgeBottom).LineStyle = xlNone
            If .Cells(i, "A") <> .Cells(i - 1, "A") Then .Range(.Cells(i - 1, "A"), .Cells(i - 1, "K")).Borders(xlEdgeBottom).Weight = xlMedium
        Next i
    End With

End Sub

 

[Donai]

Question, can the code work out the last column of data rather than use K?

 

[rpaulson]

LC = Range("IV1").End(xlToLeft).Column

 

[Donai]

This does not work if couple of columns is blank, can the code workout the header row which is row 5, i need the line to go as far as the end column of last header column

LC = Range("IV1").End(xlToLeft).Column