TRIM SUM - Row delete - Color Row based on Value

[Frick]

I've been using a worksheet that has a TRIM VBA function that works well. I copy and paste the data each month from another program into the DATA tab. Then, click on the TRIM macro button and the data is set out in the DATA2 worksheet. All has worked well until some recent changes to the input data that now requires an adjustment to the VBA code, which now requires some help.

I have attached a sample workbook with data for reference and the VBA code used.

Base on the data pasted in worksheet 1 [DATA], I need the VBA to:
1. Remove all the SUBTOTAL rows
2. Remove all the blank rows
3. Complete the TRIM function with the changes into worksheet 2 [DATA2]
4. Highlight the rows [Highlight color "Yellow"] where the accrual amount [Col I] is negative amount
5. Sum the data amount in col I based on FUND col [A] reference #

Their is one change needed in the summing VBA function in worksheet 2 [DATA2]. That is where the fund code equals 0160A1 and the ACC [Col D] starts with "SP". What is needed is for the a sum for 0160A1 and a sum for 0160A1 - SP.

Hopefully, the existing code for the TRIM function and the sum just needs some minor adjustment.

As always thank you for all help provided.

[Joske920]

hi,

i can help you with the vba

Please Login or Register  to view this content.

[sintek]

Is there any reason why you need sheet2...Why not just convert sheet1 automatically

Please Login or Register  to view this content.

[Frick]

Thanks for the help. All of the criteria in my post 1 through 5 have been met. The Subtotal rows have been removed, blank rows removed, TRIM function still works, the less than zero amounts are highlighted in yellow, and each fund reference number aggregates the amounts.

However, the VBA code for the last criteria is still not written. When totaling the fund code 0160A1 [Col A] there needs to be 2 totals: 1 for 0160A1 where Col D does not have "SP" as part of the code and 1 for 0160A1 where Col D HAS "SP as part of the code. That is the last piece of the puzzle.

Looking forward to finalizing this one. Thanks for all the help.