VBA to Sum a Column from a fixed cell up until the first blank cell

Question

I have a column of numeric data, where I need to sum the values from a specified cell, down to the last value before the first blank cell in the Column. I have used a Range function previously to complete this, but on this occasion the number of rows before the blank cell is unknown and cannot be defined in a range. the simple explanation is I need the result in cell A1 to be

=sum (A6:AX)

where X is one before the first blank cell.

I will then write this into my VBA loop to complete it for nth columns over nth sheets.

Answer 1

Use the following sub:

Sub SumTillBlankCell()
Dim BeforeFirstBlankCell

  BeforeFirstBlankCell = Range("A6").End(xlDown).Row
  Range("A1").Value = Application.Sum(Range("A6:A" & BeforeFirstBlankCell))

End Sub