Totaling a column that changes length with blank cells

[ukatama]

I have a quotation spread sheet that needs to have the dollar value totaled. But the column with the values in changes length and format depending on the items quoted, the values start in cell J20 and can be any length. There are also blank cells in the column. What VBA code will handle this task; for the record I?m somewhat new to VBA, and I?m teaching myself and this one has me a little confused. I do have a Total cell copying to the last row, but it is only picking up the bottom value not passing the blank cells.:help

[Jacob Hilderbrand]

Would something like this work for you?


Dim Total As Double
Total = Application.WorksheetFunction.Sum(Range("J20:J65536"))

[ukatama]

Thanks I will give it go

[geekgirlau]

Jake, wouldn't this create a circular reference if the total is also in column J? You could do something like the following:



Range("MyTotal").FormulaR1C1 = "=SUM(R20C:R[-1]C)"

[ukatama]

Thanks for the code, I tried it and it worked by giving me the resuslt I wanted. But then the total was repeated down the page for little over 800 lines. and doubling the total each cell as it moved down the sheet. I have no idea why this is happening.

[Jacob Hilderbrand]

I am not 100% sure what ukatama really wants. There would not be a circular reference, but if there was a total in a cell and this code was used then the value would be doubled.

[ukatama]

I had items to sum in cells J20,26,28,34,40 and 46 with the total in cell J52. Then from cell J53 to cell J933 there was a total that was doubling as it went down the sheet, cell by cell. But as I said the total was perfect in cell J52, just what I wanted, all I need now is to stop the script writing the additional lines to cell J933.

But thanks we are getting closer.

[Jacob Hilderbrand]

Can you post the code?

If you are trying to determine the last row (which in this case is 52) you can use this.


LastRow = Range("J65536").End(xlUp).Row

[ukatama]

This is the first time I have been on this or any site like this. And I'm having problems locating how to attached the code I'm using, could you please guide me?

[Jacob Hilderbrand]

You can just copy and paste the code into your message. Also if you use VBA tags it will be formatted like it would be in the VBE.

To use VBA tags put (VBA) at the beginning of your code and (/VBA) at the end. But instead of parentheses use square brackets [ ].

And you can also attach a Zip file if you want. Just click on Post Reply (or Go Advanced) then scroll down and look for Manage Attachments.

[ukatama]

This the section of the code that brings in the total line and places the total for everything from J20 to the cell the total ends up in.


Sub CopyOneArea3()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Dim Top As String, Bot As String, Fx As String
Dim OSet As Integer
Lr = LastRow(Sheets("New Quote")) + 1 'position on the sheet where the imported quote Total wording is entered.
Set sourceRange = Sheets("Remarks").Range("H15:J15") 'Location of Total wording.
Set destrange = Sheets("New Quote").Range("H" & Lr) ' ditto above
sourceRange.Copy destrange
Range("MyTotal").FormulaR1C1 = "=SUM(R20C:R[-1]C)" 'Code sent by geekgirlau
End Sub

[Jacob Hilderbrand]

What does the range "MyTotal" refer to?

Is Lr the row you want to put the formula into?


Range("J" & Lr).Value = "=Sum(J1:J" & Lr - 1 & ")"

[ukatama]

Fantastic it works!!! Thanks a million.

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care