Macro for finding rows and bordering them

[ph900921]

Hello! Heard a lot of good things about this forum and had a macro question that I was hoping someone could help me on.

I have spreadsheets where countries are listed from B2 down and data on these countries are listed in the columns across, and with totals at the bottom.

Countries data1 data2 data3
a x x x
b x x x
c x x x
Total x x x
I am making a macro to format this to look like this:

Countries data1 data2 data3
a x x x
b x x x
c x x x
Total x x x

using borders, but since the number of countries changes every week, I dont know how to get the macro to find the last line and place a border above it (or the 2nd last line and place a border below it would work too I guess).

The second thing is, I already have an existing macro that does other formatting. Is it possible to take this macro and place it in the middle of the existing macro? Thank you very much for your help!

edit: err formatting didn't work but I hope you get the picutre. the x are for the data values and the 2nd line should extend to the end

 

[DebugGalpin]

One way would be to use an Offset function as that will change the table dynamically.

Formulas > Name Manager > New

To get the first row
Name: FirstRow
Refers to =Sheet1!$A$1:$D$1

To get the row above the total:
Name: Total Row
Refers to: type"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!A:A)-1,4)"

that makes the range dynamic.

Then the macro would be:-


' Macro1 Macro
'
'Formats 1st row
Range("FirstRow").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone


'Formats TotalRow
Range("TotalRow").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
End Sub

 

[DebugGalpin]

Sorry missed the ref to cell B2. Change the offset formula appropriately to name the correct range and all should be fine

 

[ph900921]

works perfectly! thank you very much!

one more question: if I have the same thing going on in 5 tabs or so, is there a better way to make it apply to every tab other than to name the sections for each tab and referencing them all in the macro?

 

[DebugGalpin]

You could change the first section on each one to read

Sheets ("Sheet 1").Select
Range ("A1:D1").Select

rather than Range("FirstRow")


That would cut down on naming the first part of it, not too sure about the others. Obviously if they were all the same length then it wouldn't be a problem, a macro could just copy and past formats from sheet 1 into all other sheets.