How to identify usedrange in each column and insert it into a formula?

Question

Can someone please advise how to identify usedrange in each column and insert it into a formula?

The problem is that I have around 1300 columns, with each column having a different number of populated cells (between row 1:380). So, e.g. Column A data range may = to A7:A251, Column B data range may = to B68:B94 and so on.

What I need to do is to pull the RANGE for each column (I know what to do with the rest) e.g. D45:D195.

The formula is non-standard for the excel so please ignore the "ACF" bit. Standard ACF formula in my add-on should be =ACF(RANGE,1)

I have started working on something but I don't think it does what I need as it only pulls the number of the last populated row.

Sub Range1()

Dim LastRow As Integer

LastRow = ActiveSheet.UsedRange.Rows.Count

ActiveSheet.Range("G1") = LastRow

ActiveSheet.Range("H1").Formula = "=ACF(" & "D" & LastRow & ":" & "D" & ",1)"

End Sub

SJR SJR · Accepted Answer · 2019-04-08T12:50:12

This should take into account differing start and end cells in each column.

You find the first used cell by starting at the last row of the sheet and looping forward to the top.

You find the last used cell by starting at the first row and looping back to the bottom.

Sub Range1()

Dim c As Long, r1 As Range, r2 As Range, r As Range

For c = 1 To 10
    Set r1 = Columns(c).Find(What:="*", after:=Cells(Rows.Count, c), searchdirection:=xlNext) 'first used row
    Set r2 = Columns(c).Find(What:="*", after:=Cells(1, c), searchdirection:=xlPrevious)      'last used row
    Set r = Range(r1, r2)                                                                     'used range
    Cells(r2.row + 1, c).Formula = "=ACF(" & r.Address & ",1)"
Next c

End Sub

How to identify usedrange in each column and insert it into a formula?

1 Answers