How do i create a range between two variable points in VBA?

Question

I'm trying to write code which automates something: I've got a table of data which I need to add a column into, then put a sum in which goes all the way down to the bottom row of data and no further. I know how to define the bottom row as a variable; but what if the column I'm entering the data can vary too? In my example, the column I want to do the sums in is always to the left of the column entitled '16'. It will always start at row 2, but it won't always be column O. It might be column P, or Q, for example.

Sub enter_column_and_add_calculations()

    Dim NBottomrow

    Call find_bottom_row
    NBottomrow = ActiveCell.Row
'find column entitled '16':
    Range("A1").Select
    Cells.Find(What:="16", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,         
    MatchCase:=False _
        , SearchFormat:=False).Activate
'insert new column to the left:
    Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'insert text in the cell:
    ActiveCell.FormulaR1C1 = "OOT Debt"
'offset one cell below:
    ActiveCell.Offset(1, 0).Range("A1").Select
'i'm now in the cell i want my range to start at. In this example it's cell O2, but it often varies:

    ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[5])"
    Selection.AutoFill Destination:=Range("O2:O" & NBottomrow)

End Sub

Private Sub find_bottom_row()

    Range("A1").Select
    Selection.End(xlDown).Select

End Sub

Many thanks for your help :-)

If you look at the line Selection.AutoFill Destination:=Range("O2:O" & NBottomrow) I don't always want the range to start from O2. Sometimes it's P2, of Q2, for example. I want to factor that variation in. — Luke Redfern, Sep 18 '18 at 09:47
Well you might benefit from reading up on: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba, as most of your problems originate from the use of ActiveCell — Luuklag, Sep 18 '18 at 09:49

score 0 · Answer 1 · 2018-09-18T11:20:11.390

0

Try,

Sub enter_column_and_add_calculations()

    dim m as variant, lr as long

    with worksheets("sheet1")

        m = application.match(16, .rows(1), 0)
        if iserror(m) then exit sub

        lr = .cells(.rows.count, m).end(xlup).row

        .cells(lr+1, m).formula = "=sum(" & .range(.cells(2, m), .cells(lr, m)).address(0,0) & ")"

    end with

end sub

edited Sep 18 '18 at 11:20

answered Sep 18 '18 at 09:53

For teaching purposes, using more descriptive variable names than `m` and `lr` would be very helpful. Additionally, just before `End With`, there is a line with just a `.` on it - is something missing or is this just a typo? – FreeMan Sep 18 '18 at 11:16
Good catch, I thought that was a bug on my screen. But no, I prefer to use a minimum of typing. Seeing vars like `dim theLastRowInColumnC as long` is just ridiculous and if someone cannot figure out that `lr` stands for `lastRow` then maybe they're in the wrong job. I do however, type out `vbNullString` instead of `""`. – Sep 18 '18 at 11:22
1

`lastRowInColumnC` is rather long, but there's no doubting exactly what it is! :) If you also need to know what the `lastRowInColumnD` is, those both help. Again, in a teaching opportunity (as most of these answers are), somewhat more descriptive names are useful for the OP. Oh, that wasn't a bug on your screen, it was a bug in your code! :D – FreeMan Sep 18 '18 at 11:27
Thanks. I've given it a go - It looks like it works, but I think it's a bit beyond me to add the other bits to it that I need. Thanks anyway for your help. I've learned something. – Luke Redfern Sep 18 '18 at 11:52

How do i create a range between two variable points in VBA?

1 Answers1