*RESOLVED* last cell

[brianbaart]

This is my code:

Dim lastcell As String
 
    ActiveWorkbook.Save
    
    lastcell = (ThisWorkbook.Worksheets("Hoofdstukken").Cells.SpecialCells(xlCellTypeLastCell).Row) + 1
    
    ThisWorkbook.Worksheets("Hoofdstukken").Range("L" & lastcell).Select
    ActiveCell.Formula = "=SUM(" & "L2" & ":" & "L" & lastcell - 1 & ")"
    
    ActiveCell.Offset(0, 1).Activate
    ActiveCell.Formula = "=SUM(" & "M2" & ":" & "M" & lastcell - 1 & ")"

The user enters some information in the cells with a form I have made.
Then pressing a button the above code is used.

But the problem is when the user delets some rows the lastcell value is stil the "old" one. So the totals are placed to low on the spreadsheet
Even when I save the workbook first.
Thanks in advance,
Brian

[VBAhack]

Looks like to need to calculate lastcell after the rows are deleted by the user. Also, the cell's don't have to be selected in order to change their value:

With ThisWorkbook.Worksheets("Hoofdstukken").Range("L" & lastcell)
    .Formula = "=SUM(" & "L2" & ":" & "L" & lastcell - 1 & ")"
    .Offset(0, 1).Formula = "=SUM(" & "M2" & ":" & "M" & lastcell - 1 & ")"
End With

VBAhack

[brianbaart]

Thank you for answering,

But maybe I wasn't clear in my question.
The user fills the cells then presses the button to put the "total" formulas in the last cells. (number of rows is for example 35)
Then he clears row 20 to 40 (including the sums) making everything below 20 empty.
And presses again thus recalculating the last cell.
The totals are now placed in row 35 and not in row 21 (20 + 1).
I thought that it had anything to do with the undo function.
That's why I saved the workbook before calculating the last cell. But that doesn't do the trick either.

With kind regards,
Brian Baart

[RobDog888]

How are you clearing the rows? They may not actually be deleted. That
could be one reason that the last row is still being detected at 35.

[brianbaart]

At this moment it is done manually by selecting the rows on the left of the screen then I delete them. (not remove them, I haven't tested that jet)
After that I select the cel A1 and press the button. And the cells are placed at the row 35 also the sums are from cell L2 to L34 Which means that my code works but not the Xl-lastcell.
I have also tried the:
do until activecell.value = Empty
activecell.offset(1,0)
loop
But the problem with this is that my coloms have empty cells between them,
I mean L3 has a value, L4 is empty, L5 has a value. So the loop stops at L4.

Thanks for helping me,
Brian Baart

[RobDog888]

Hmm... I see. How about doing a .Refresh? It could be that since you
are manually deleting the rows the excel application object is not detecting it
so it thinks there are still 35 rows in the SS. Yo could also do the delete rows
from VB so the application will be the one deleting the rows and that should
make it remember they are not there anymore.

You could also try clearing your variable to zero before you execute the
specialcell....

[brianbaart]

Thanks for the reply,

Empting the variable could be a solution, although the variable is given a new value everytime the user clicks the button. (but I will look into it)

Deleting the rows from code is not a solution as I do not know which rows to delete but all and that may or may not be the solution

But a .refresh is most certainly a possiblility.

I will continue tomorrow as it is near 24:00 here in Holland and tomorrow is another day.
I will be back.

Thanks,
Brian Baart

[RobDog888]

Ok, I asked because even if the cells look empty they may contain some
value that the specialcells method is picking up. Same here kind of, I going
home for the day. I will be here tonight and tomorrow during the day.

Later.

[brianbaart]

Strange, very strange.

When I switched the computer on this morning and tried the program the lastcell value was still 35.
Which means that the cells in between are not empty according to excel.
Then I removed the rows (not delete them) so that the rows below are moved up.
And it works...............
Don't know why but it does.
Thanks everybody (RobDog888)
Brian Baart