# Calculating from a previous cell if Blank

Closed
Trina - Aug 10, 2011 at 10:11 AM
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Aug 25, 2011 at 09:53 AM
Hello,
I'm working on a travel excel spreadsheet that calculates different dates based on time it takes to get to a location and how long you want to spend there.

Problem is, if I delete a location row, the formula for the next location is messed up because the data is no longer available ( ie Cell B23 pulls from D22, but if it is blank, it is unable to calculate).

Is there a way the formula (or if necessary to use a macro) can find the last populated cell in Column D to calculate the next date?

Thanks very much!

Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Aug 11, 2011 at 10:02 AM
Hi Trina,

Try this macro to see if it fits your request.
Remember to save your workbook first.

Sub DeleteRow()
Dim x As Integer
Dim y As Integer
x = InputBox("Please insert the row number you wish to delete:", "Delete row")
y = Range("A" & Rows.Count).End(xlUp).Row
Rows(x).ClearContents
Range(Rows(x).Offset(1, 0), Rows(y)).Copy
Rows(x).PasteSpecial
Rows(y).ClearContents
Application.CutCopyMode = False
End Sub

Best regards,
Trowa
I dont want to delete a row. It is just if a row happens to be deleted by a user because that place is no longer being visited. Will this still work?
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Aug 25, 2011 at 09:53 AM
Hi Trina,