-1

I have over 1000 columns in my dataset. They are all ending in row 375.

Can someone advise how do I create a VBA code which enters a formula in e.g. A376 and instead of the range being e.g. A1:A375 it looks for the last instance of data (e.g. A301) and the first instance of data in the same column (e.g. A178) and enters the formula (for the example AVERAGE) =AVERAGE(A178:A301)?

The same would have to happen for all 1000 columns (some kind of loop I guess).

I'd be greatly obliged if someone could advise. Thanks

[![enter image description here][1]][1]

[![enter image description here][2]][2]

Pierre Bonaparte
  • 623
  • 6
  • 17
  • Start [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) maybe. Well, what do you mean by the "last instance of data"? Are you dealing with formulas? – BigBen Aug 26 '20 at 17:37
  • The last instance of data in range A1:A375 for column A is in A301 (the last instance of data = last populated cell) but the first populated cell in column A is A178. It varies across all columns (it's the stock data from Yahoo Finance for many different stocks i.e. over 1000 columns. Sometimes data may start at B2:B370 or in C100:C375. – Pierre Bonaparte Aug 26 '20 at 17:42
  • Then the link I provided is what you need. – BigBen Aug 26 '20 at 17:46

1 Answers1

2

This should work for you. It is dynamic, meaning that the exact row count does not matter. It will add the formula to the next row after the last of the data. It assumes your headers are in row 1, else you will need to adjust Range("XFD1").End(xlToLeft).Column appropriately.

Now you will need to call the method like so:

test ActiveWorkbook.Worksheets("Sheet1")

or better yet, use the worksheet property (name) found in the properties window in the vba editor when the sheet is selected

test Sheet1

updated code:

Sub test(sht As Worksheet)
    Dim c As Integer
    Dim startCell As Range
    Dim endCell As Range
    Dim lastRow As Integer
    
    lastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Offset(1, 0).Row
    
    For c = 1 To Range("XFD1").End(xlToLeft).Column
        If IsEmpty(sht.Cells(2, c).Value) = False Then
            Set startCell = sht.Cells(2, c)
        Else
            Set startCell = sht.Cells(1, c).End(xlDown)
        End If
            
        Set endCell = sht.Cells(1048576, c).End(xlUp)
        
        sht.Cells(lastRow, c).Formula = "=AVERAGE(" & startCell.Address & ":" & endCell.Address & ")"
    Next c
End Sub
Jeremy Hodge
  • 612
  • 3
  • 14