(Solved) – VBA give different last row when trying to sum a specific row


I hope you could help. I am trying to build a model to estimate the profit of multiple products.
In order to do so, I have to find the last row, and then sum the range of the first row to the last row.

First, I select the quantity from a previous cell, A6 for instance, the calculations are made in cell F6.
The amount of rows is always different. In order to determine the last row I do this:

**LastRow = shmodel.Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row**

**Dim lastrowF As Long
lastrowF = shmodel.Cells(Rows.Count, 3).End(xlUp).Row
I start by a simply formula, shmodel.Range("D6").FormulaR1C1 = "=RC[-3]"
shmodel.Range("F6").AutoFill Destination:=Range("F6" & ":" & "F" & LastRow)**

This formula is easy and works.

Now I need to calculate the total of that row, lets assume we have 12 rows.
Hence, VBA should identify row 12 as the last row, and should sum row 6- row 12 in F13.

**Dim cel1 As String, cel2 As String
cel1 = shmodel.Range("F6").Address
cel2 = shmodel.Range("F" & (lastrowF - 1)).Address
shmodel.Range("F" & lastrowF).Formula = "=sum(" & (cel1) & ":" & (cel2) & ")"****

For some reasons, this code works for most of columns, but in some instance, VBA select the wrong last row, and so the SUM formula is incorrect. In most cases VBA sees the first row as the last row.

Next, I need to calculate the sum of a column when sometimes a row is empty. I use a vlookup to identify some costs, hence, row D10 is empty , but row 11 is not. The idea is to define the last row, which is D11, calculate the sum of row 6- 11. How do I calculate the sum correctly?

I have tried many options, but nothing really helped.

Many thanks in advance.


Leave a Reply

Your email address will not be published. Required fields are marked *