Calculating from a previous cell if Blank

Closed
Trina - Aug 10, 2011 at 10:11 AM
TrowaD
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!


1 reply

TrowaD
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
0
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?
0
TrowaD
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,

Could you upload your file using a filesharing site like www.speedyshare.com and post back the link.
Please re-explain what might occur and how you want Excel to react to it.

Best regards,
Trowa
0