Calculating from a previous cell if Blank

Closed

Trina - Aug 10, 2011 at 10:11 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 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!

Calculating from a previous cell if Blank
If cell contains date then return value ✓ - Office Software Forum
If cell contains specific text then return value in another cell ✓ - Excel Forum
Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
Excel: If Date =, then enter a value ✓ - Excel Forum
Excel "IF" function w/ date in test cell ✓ - Excel Forum

1 response

Answer 1 / 1

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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

Trina
Aug 24, 2011 at 04:48 AM

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?

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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

Similar discussions

Formula to Find&replace blank cells in excel

Counting cells containing only numbers

How to use conditional format based on text?

Excel - IF function with multiple words

Highlight cell if another cell contains specific text

Similar discussions

Subscribe To Our Newsletter!