Calculating from a previous cell if Blank Closed

Question

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!


Configuration: Windows XP / Internet Explorer 8.0

TrowaD · Answer

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

Calculating from a previous cell if Blank

1 response

Similar discussions

Subscribe To Our Newsletter!