CODEWITHSUNDEEP
excelvba
998024562165
998024562165

Reputation: 3

VBA & Excel: Selecting the row below a selection

I know this question may get asked a lot, but I haven't been able to find or understand the answer for exactly what I am looking for.

I am learning VBA in excel for the first time today and I am trying to auto-format a table of values and want this to work on different range sizes.

I am stuck on how to select the row underneath the last row in my selection and format it.

My code so far is:

Selection.CurrentRegion.Select

Selection.Rows("1:1").Interior.Color = 12155648

With Selection.Rows("1:1").Font

    .ThemeColor = xlThemeColorDark1

    .Bold = True

End With



Selection.CurrentRegion.Select

Selection.Cells(Selection.Rows.Count, Selection.Columns.Count).Select

   

Selection.Interior.Color = 12632256

Selection.Font.Bold = True

Selection.Range("A1").Value = "Total"

What I want to happen:

Original enter image description here

Desired Formatting

enter image description here

Upvotes: 0

Views: 1861

Answers (3)

Variatus
Variatus

Reputation: 14383

No, it isn't a common question because most programmers learn on their second day (that's tomorrow in your schedule) not to "Select" anything and use the Range object instead. Then your code would look more like this:-

Private Sub Snippet()

    Dim Rng     As Range

    With Selection.CurrentRegion
        .Rows(1).Interior.Color = 12155648
        With .Rows(1).Font
            .ThemeColor = xlThemeColorDark1
            .Bold = True
        End With
        Set Rng = ActiveSheet.Cells(.Row + .Rows.Count, .Column).Resize(1, .Columns.Count)
    End With
    
    With Rng
        .Interior.Color = 12632256
        .Font.Bold = True
        .Cells(1).Value = "Total"
    End With
End Sub

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53166

What about making it into an actual table

Sub Demo()
    With ActiveSheet.ListObjects.Add(xlSrcRange, ActiveCell.CurrentRegion, , xlYes)
        .Name = "MyTable" ' optional
        .ShowTotals = True
    End With
End Sub

Before

enter image description here

After

enter image description here

Upvotes: 1

Kin Siang
Kin Siang

Reputation: 2699

You may use the following method, assuming your table start from B4:

Sub ty()

Dim lastrow As Long

lastrow = Sheet1.Range("B4").End(xlDown).Row + 1

With Sheet1.Range("B4").Resize(1, 5)
    .Interior.Color = 12155648
    .Font.ThemeColor = xlThemeColorDark1
    .Font.Bold = True
End With

Sheet1.Cells(lastrow, 2).Value = "Total"

With Sheet1.Cells(lastrow, 2).Resize(1, 5)
    .Interior.Color = 12632256
    .Font.Bold = True
End With


End Sub

Upvotes: 0

Related Questions