Problems with formatting last row with data from column A to S

[awasi]

Hey guys,

I hope you can help me since I cannot seem to find the piece of code necessary to perform the following action.
Have tried multiple last-row codes but nothing worked.
I use Excel 2007 with Windows XP

I have a set of data ranging from column A to S. The amount of collumns varies.
Already managed to pull sums/averages in the first empty cell per column. This row (with the sums and averages) needs to be in different colours now, just like the first row in the sheet.
The first row was pretty easy and looks like that:

Range("A1:S1").Select With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

I need to perform the same formatting for the last row with data (sums/averages). However, since there is only data in column A to S and the amount of rows varies, I need a piece of code that only selects the last row from column A to S.

I appreciate your help very much!

Regards
Awasi

 

[Rick Rothstein]

Consider this previous posting of mine. For your particular question, change the Cells to Columns("A:S")....

For last value
---------------
LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column

For last value or formula
------------------------------
LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row

LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column

A note about those headings... the ones labeled "For last value" means it will return the last used row and column for a displayed value (whether a constant or one from a formula) even if there are formulas after those cells displaying the empty string (""); whereas those labeled "For last value or formula" returns the last cell with either a value or a formula even if that formula is displaying the empty string ("").

 

[awasi]

Hey,
thank you very much for your reply.
Could you tell me how to connect the two so that I select the last row from column A to S?
I have tried several variations of the below bold line but cant seem to find the right one.
I am a real beginner, so it would be great if you could help me out once more.

LastUsedRow = ActiveSheet.Columns("A:S").Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastUsedCol = ActiveSheet.Columns("A:S").Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column
Range("LastUsedRow:LastUsedCol").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

 

[MARK858]

I am sure Rick won't mind me jumping in... try

Sub test()
    Dim LastUsedRow As Long, LastUsedCol As Long


    LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
                                         SearchDirection:=xlPrevious, LookIn:=xlValues).Row


    LastUsedCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                                         SearchDirection:=xlPrevious, LookIn:=xlValues).Column


    With Range(Cells(LastUsedRow, 1), Cells(LastUsedRow, LastUsedCol))
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.499984740745262
            .PatternTintAndShade = 0
        End With
        .Font.ThemeColor = xlThemeColorDark1
    End With
End Sub

 

[awasi]

Wow, works perfectly fine.
I would have, by no means, been able to firgure that out myself.
Thank you very much guys! You saved my day