Thread: Modify Formula to Find Last Column

Hi, I'm using this formula to find the range of used cells and then offset to a specific columna and insert a formula:

With Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, 26)
    .FormulaR1C1 = "=IF(RC[-1] = """", """", TODAY()-RC[-1])"

Works great - but how can I determine programmatically where the last column is instead of using offset? I've been messing around (see below) but while this doesn't throw an error it also doesn't do anything.

With Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset.Cells(Columns.Count, "A").End(xlToRight)
    .FormulaR1C1 = "=IF(RC[-1] = """", """", TODAY()-RC[-1])"

Need to find the last column in row 1 and then offset 1 (to a blank column) (or find first blank column)

Try this

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    'or
    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

Ok, you can use the following to find the last column in general, i'm using a slightly different statement.

The key is, you can use "columns.count" in the column entry just like you used rows.count. Also, you can use the End mode to travel left from the furthest column but stating .end(xltoleft) -- just like you used .end(xlup) to find the last row.

Range("A2" & ":" & Cells(your_row_number, Cells(2,columns.count).end(xlToLeft).address))

Hmm... using xltoright from the last column with an offset statement is definitely going to give problems - if it worked it would be offsetting your range right off the worksheet!

I tried this (put in a comma you were missing and used xltoleft). seems to work (not exactly sure what you are doing though).

check the attachment too.

With Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, Cells(1, Columns.Count).End(xlToLeft).Column + 1)
    .FormulaR1C1 = "TEST"
End With

Is this what you are trying to do?

Sub DaysElapsed()
    Dim LastRow As Long, NextCol As Long

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

    With Range(Cells(2, NextCol), Cells(LastRow, NextCol))
        .FormulaR1C1 = "=IF(RC[-1] = """", """", TODAY()-RC[-1])"
    End With
 
End Sub

an alternative might be

Sub DaysElapsed()
    Dim LastRow As Long, NextCol As Long

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

    Cells(2, NextCol).Resize(LastRow - 1, 1).Formula = "=IF(RC[-1] = """", """", TODAY()-RC[-1])"

End Sub

Your question was

..... but how can I determine programmatically where the last column is instead of using offset?

As far as I can see GeneralDisarrays' solution uses offset ...
Also

Offset(, Cells(1, Columns.Count).End(xlToLeft).Column + 1)

This skips a column and returns the formula in the second available blank column so your formula will always return "", remove the +1 and all will be okay.