Using Range.Address to specify the high end of the range

Question

If I'm getting the result $F$1:$F$21 from using .Address on a Range, is there a way to seperate just the F21 part and then use .Offset to tell excel the cell I want to put a value in?

Public Sub TotalAudits()
    With Worksheets(1)
        'Dim lastrow As Long: Set lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Dim celTotal As Range: Set celTotal = .Cells(.Rows.Count, "A").End(xlUp).Row.Offset(2, 4)
        celTotal.Value = "Monthly Totals"
        With celTotal
            .Font.Bold = True
            .Font.Italic = True
            .HorizontalAlignment = xlRight
        End With

        Dim rngMonth As Range
        For Each rngMonth In Range("F1:Q" & .Cells(.Rows.Count, 1).End(xlUp).Row).Columns
            Dim colTotal As Integer: colTotal = rngMonth.Cells.SpecialCells(xlCellTypeConstants).Count - 1
            .Range(Split(rngMonth.Address(False, False), ":")(1)).Offset(2, 0).Value = colTotal
            'MsgBox (rngMonth.Address)
        Next rngMonth
        'MsgBox (lastrow)
    End With
End Sub

Specifically, this area of code....

MsgBox (rngMonth.Address)

I want to offset the last part of the address by 2 rows and insert the value stored in colTotal in that offset cell.

Note: your method of finding the last row is unreliable. See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to. — BigBen, Aug 02 '19 at 14:55

Mikku · Accepted Answer · 2019-08-02T15:30:10.580

1

As BigBen Said you should change the way you are calculating the LastRow, try something like this:

lastrow = .Cells(.Rows.Count, "A").End(xlUp).row

Just to get F21 out of rngMonth.Address, use

Replace(split(rngMonth.Address,":")(1),"$","")

Or:

Split(rngMonth.Address(False, False), ":")(1)

Final Line you would want to use is:

Range(Split(rngMonth.Address(False, False), ":")(1)).Offset(2, 0).Value = colTotal

Complete Code:

Public Sub TotalAudits()

    With Worksheets(1)
        Dim lastrow As Long: lastrow = .Cells(.Rows.Count, "A").End(xlUp).row
        Dim celTotal As Range: Set celTotal = .Range("A" & lastrow).Offset(2, 4)
        With celTotal
            .Value = "Monthly Totals"
            .Font.Bold = True
            .Font.Italic = True
            .HorizontalAlignment = xlRight
        End With

        Dim rngMonth As Range
        For Each rngMonth In Range("F1:Q" & .Cells(.Rows.Count, 1).End(xlUp).row).Columns
            Dim colTotal As Integer: colTotal = rngMonth.Cells.SpecialCells(xlCellTypeConstants).Count - 1

            MsgBox (rngMonth.Address)
            Range(Split(rngMonth.Address(False, False), ":")(1)).Offset(2, 0).Value = colTotal

        Next rngMonth
        MsgBox (lastrow)
    End With
End Sub

edited Aug 02 '19 at 15:30

answered Aug 02 '19 at 14:58

Mikku

6,538
3
15
38

What are the two False arguments for, if you don't mind my asking? – mongoose00318 Aug 02 '19 at 15:12
Dim lastrow As Range: Set lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row - I get an error: Object required on that line – mongoose00318 Aug 02 '19 at 15:13
They are so you get the Stripped Address. i.e without `$` sign – Mikku Aug 02 '19 at 15:14
Oh ok cool. What does that $ represent anyhow? Been something I've been meaning to learn but just haven't got there yet. – mongoose00318 Aug 02 '19 at 15:14
Use `Dim lastrow As Long: Set lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row` – Mikku Aug 02 '19 at 15:15
$ Represents that the address is Fixed, it won't move if you drag the Formula. Basically to fix references in an Address `$` is used – Mikku Aug 02 '19 at 15:16
Actually, don't use the LastRow code, it's gonna throw your code off on various Angles, too much of changes are required. Because you have used it as a Range afterwards. – Mikku Aug 02 '19 at 15:18
Just updated the code, but still getting the same error just on the next line as I moved the statement down. – mongoose00318 Aug 02 '19 at 15:21
Same thing with your code...Object required on: Set lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row – mongoose00318 Aug 02 '19 at 15:23
2

@Mikku you're trying to set a long variable as an object - remove the `Set` i.e. `Dim lastrow As Long: lastrow = .Cells(.Rows.Count, "A").End(xlUp).row` – Tom Aug 02 '19 at 15:25
Oops. Yep that did it. – mongoose00318 Aug 02 '19 at 15:26
1

Also, you've got `celTotal.Value = "Monthly Totals"` just above your `With celTotal` why not move it inside the `With` block? – Tom Aug 02 '19 at 15:27
1

Thanks @Tom , Saved me couple of times today :) – Mikku Aug 02 '19 at 15:30

score 1 · Answer 2 · answered Aug 02 '19 at 15:08

You can get the last cell of a range using the following

Dim rngMonth As Range
Set rngMonth = Range("F1:F21")

MsgBox rngMonth.Cells(rngMonth.Cells.Count).Offset(2, 0).Address

This selects the last cell in a range, offsets it be two rows and returns the address

Using Range.Address to specify the high end of the range

2 Answers2