Rearrange format with "TRIM" and "SUBTRACT" functions

Hi Excel Gurus,

It’s been a while since I last logged to in ask for help here. I have always been able to survive using the built-in macro recorder. But I don’t think I’m gonna be able to do that on this template.

Template Background:

We have recently moved to a new reporting platform and the extracts are no exactly excel-friendly. I’m attaching a sample for those who want to extend a helping hand.

Things I have so far:

1. Move rows 1 to 3 from column A to column B. Code below is something I have recorded.

    Range("A1:A3").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

2. Delete all blank rows after row no. 5. This is something I picked up on when I joined the forum. I'm not sure though on how to instruct the macro to search for blank rows after row 5.

Dim i As Long, c As Integer

        For i = Selection.Rows.Count To 1 Step -1
            c = WorksheetFunction.CountA(Selection.Rows(i))
            Select Case c
            Case Is = 0
                Selection.Rows(i).EntireRow.Delete
            End Select
        Next i

3. Delete all blank columns. I'm planning to use the code above for the blank columns as well. Will this work?

Dim i As Long, c As Integer

        For i = Selection.Columns.Count To 1 Step -1
            c = WorksheetFunction.CountA(Selection.Columns(i))
            Select Case c
            Case Is = 0
                Selection.Columns(i).EntireColumn.Delete
            End Select
        Next i

This is what i'm currently stuck with:

1. adding a new column in between "No." and "ID" named "Trim" and another column at the end named "Total"
2. place a TRIM function in "Trim" column to remove trailing spaces on the entries in "ID" column
3. place a function in "Total" column that subtracts entries from "Total Sales" with entries in "Discount"
4. get the sum of all the five columns: "Total Sales", "Incentive", "Discount", "Premiums", "Total"


Thanks in advance to those who will help. Have a great day!

bumping post...

thanks...

bumping post