We help IT Professionals succeed at work.

Need help with Microsoft Excel VBA macro that will delete a row based on value found along with adding calculations before doing so.

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
Need help with Microsoft Excel VBA macro that will delete a row based on value found.

Please note ee sample-del.xlsx (Sheet1 only)) file as I ask this question.   The ee samplefinished-del.xlsx is the final version that completes as my answer.

Basically I identify what row has the value 'Prior' in it and want to take the next row's totals and add them to all on that prior row - then delete that next row entirely.

The columns may be more than just 4 columns shown or be less.  So I can't just loop through a specific set of columns as in this example is just Column D.  The next time I run the macro there could be columns all the way to M or the next one could be column A only.  I need it smart to identify to only add the columns that have information.

This is a 2 of 3 part series of questions I have.
Part 1 is found here:  (That already completes the 1st step)
https://www.experts-exchange.com/questions/29166754/Looping-through-Excel-cell-values-to-find-a-specific-value-and-then-once-found-add-new-row-that-contain-sum-calculations-for-all-columns-using-VBA.html#questionAdd

Can someone please help me with some sample Excel VBA macro code that will make this work?  I'm including Microsoft Access VBA experts as well that probably has done this before.   Right now at this time do not want links to other internet forums/ articles that may guide me in the right direction to solve this but just purely some sample code I can copy and paste and see if this will work.   (Currently researching other solutions for other coding problems and don't have time to look it up).
ee-sample-del.xlsx
ee-samplefinished-del.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this macro

Cells.Find("Prior").Offset(1, 1).Resize(, 2).Copy
Cells.Find("Prior").Offset(, 1).PasteSpecial , xlPasteSpecialOperationAdd
Cells.Find("Prior").Offset(1).EntireRow.Delete
Kesavan JeganarayananIT Consultant
Commented:
Here is one more Simple solution:

Sub Testing()
If Range("A1").Value = "Yes" Then
Worksheets("Sheet1").Rows("2:5").Delete
End If
End Sub


Sheet1 is your worksheet name
2:2 is from row 2 to 5

Author

Commented:
I just need some more time till lunchtime tomorrow to respond please.

Author

Commented:
today I will attempt these and test.

Author

Commented:
thanks for the replies.