Solved: macro to edit/format data in excel 2007

[Pete]

Hi

Need a macro to format a worksheet in excel to 2007 to reflect the following changes:-

1. Clear the contents of row 1 to 6

2. Delete row 1 and 2

3. Delete column A

4. Using Column P scroll down and delete any rows that are blank until end of the worksheet

5. Insert three column to the left of column A

6. Insert the following header in row 4

A: SO/Item
B: PO2/Item
C: Po1/Item

7. Concatenate the Columns A:C as follows:
A: Concatenate date in column G & H
B: Concatenate date in column I & J
C: Concatenate date in column E & F

8. remove concatenate formula in columns A:C - via copy and speical paste function

9. Columns PQR and S copy cut and paste after column C

[Simon Lloyd]

Why not post back when you have at the very least used the macro recorder to obtain most of what you are asking and then we will help you tweak it

Really thats a big ask without doing some of the work yourself.

[Pete]

thanks for the feedback simon..........

[Pete]

This is my macro less steps 4 and 9.....also i need the copy and paste function to remove the formula to work until the last row in columns ABC

[VBA]Sub Macro1()
Rows("1:6").Select
Selection.ClearContents
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("A4").Select
ActiveCell.FormulaR1C1 = "S)/Item"
Range("A4").Select
ActiveCell.FormulaR1C1 = "So/Item"
Range("B4").Select
ActiveCell.FormulaR1C1 = "PO2/Item"
Range("C4").Select
ActiveCell.FormulaR1C1 = "Po1/Item"
Range("A5").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[6],RC[7])"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[7],RC[8])"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[2],RC[3])"
Range("A5:C5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CommandBars("Stop Recording").Visible = False
Application.CutCopyMode = False
End Sub[/VBA]

[Bob Phillips]

Can you post your workbook, it is hard to see where to calculate the last row.

[mbarron]

How about this:
[VBA]Sub Macro1()
Rows("1:6").ClearContents
Rows("1:2").Delete Shift:=xlUp
Columns("A:A").Delete Shift:=xlToLeft
'step 4
Dim i As Long, lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lRow To 7 Step -1
If Cells(i, 16) = "" Then
Cells(i, 16).EntireRow.Delete
End If
Next
'end of step 4
Columns("A:C").Insert Shift:=xlToRight
Range("A4").FormulaR1C1 = "So/Item"
Range("B4").FormulaR1C1 = "PO2/Item"
Range("C4").FormulaR1C1 = "Po1/Item"
Range("A5").FormulaR1C1 = "=CONCATENATE(RC[6],RC[7])"
Range("B5").FormulaR1C1 = "=CONCATENATE(RC[7],RC[8])"
Range("C5").FormulaR1C1 = "=CONCATENATE(RC[2],RC[3])"
Range("A5:C5").Copy
Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("P:S").Cut Destination:=Range("D1")

Application.CutCopyMode = False

End Sub[/VBA]

[Pete]

thanks bmarron

works perfectly.... excellent feedback