autogrid

[Trevor3007]

good afternoon,

i hope there is someone who can solve this excel problem i have.

As I admin assistant one of my many jobs is to amend worksheets to the 'Business' format/style....

rows height = 20

column width = 14

font = Calibri

font size = 14


although I have a macro to do the above, the issue is that all the workbooks need to have a border . This is fine if the workbook only has a few / rows . But most of them have 2000+ & 26 + columns.

What I require is well out of my macro/VB knowledge ... is it possible to run some VB that will automatically find the last column & row containing text & then border the applicable & return to cell A1 & also the afore mention :-

rows height = 20

column width = 14

font = Calibri

font size = 14 .


thank you for your assistance & I look forward to your reply.

KR
Trevor 3007

 

[mumps]

Try:

Sub FormatRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    With Range(Cells(1, 1), Cells(LastRow, lCol))
        .Rows.RowHeight = 20
        .Columns.ColumnWidth = 14
        .Font.Name = "Calibri"
        .Font.Size = 14
        .Borders.LineStyle = xlContinuous
    End With
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

How about

Sub MyFormat()
   Dim lr As Long, lc As Long
   lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious, , , False).row
   lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Column
   With Range("A1", Cells(lr, lc))
      .RowHeight = 20
      .ColumnWidth = 14
      .Font.name = "Calibri"
      .Font.size = 14
      .BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic
   End With
End Sub

As you haven't said what border you want I've guessed.

As mumps beat me to it, I've changed mine to give an outer border only

 

[Trevor3007]

WOW...


thatis fantast!!1 thank you ever so much.

:}

 

[Trevor3007]

Thank you...yours works a treat too. I required all cells to have a border. Sorry if I had not stated this.

have a great day,

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Trevor3007]

NP & hope all others you have helped do so too :}