VBA & Excel: Selecting the row below a selection

Question

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

Desired Formatting

score 1 · Answer 1 · answered Jun 11 '21 at 03:00

1

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

After

answered Jun 11 '21 at 03:00

chris neilsen

52,446
10
84
123

I like where your head is at, however the point of formatting the tables as such was to match the existing formats of tables in a very lengthy annual report. And to match the formats of these tables from previous years as well. – 998024562165 Jun 11 '21 at 03:05

score 0 · Answer 2 · answered Jun 11 '21 at 02:46

0

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

answered Jun 11 '21 at 02:46

Kin Siang

2,644
2
4
8

Perhaps better: `lastRow = Sheet1.Range("B" & Sheet1.Rows.Count).End(xlUp).Row + 1` – BigBen Jun 11 '21 at 02:48
I was trying to make the macro such that it would work on any table that begins anywhere within the sheet. – 998024562165 Jun 11 '21 at 02:50
@BigBen, amazing, doing it reverse way. But there is better way to find last row when top few row is empty `Sheet1.UsedRange.Rows(Sheet1.UsedRange.Rows.Count).Row`, i find this long time ago on one of the Stackoverflow question but not able to trace it back, this is other link i got it https://stackoverflow.com/questions/40650508/find-last-row-in-range – Kin Siang Jun 11 '21 at 03:09
@998024562165, yes, you can just change the range base on your table will do, please try it out – Kin Siang Jun 11 '21 at 03:09
Nope, `UsedRange` isn't reliable, see https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba – BigBen Jun 11 '21 at 03:32
@BigBen, i never find it unreliable, as I will delete all the row before upload new data, one of favorite method is still `usedrange` until today :) – Kin Siang Jun 11 '21 at 03:43
@Kin Siang, I was aware I could change the range in the excel but if I wanted to use this on multiple different tables in a single spreadsheet I would have had to go in and edit the code each time. I was looking for a way using current region so that I wouldnt have to edit. – 998024562165 Jun 11 '21 at 15:40
It is fine, as long as you find a solution. – Kin Siang Jun 11 '21 at 15:51

score 0 · Accepted Answer · answered Jun 11 '21 at 03:05

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

This is exactly what I was looking for. Thank you sir. – 998024562165 Jun 11 '21 at 03:11 — 998024562165, Jun 11 '21 at 03:11

VBA & Excel: Selecting the row below a selection

3 Answers3