How do I ? Macro - cont'd from 5/9 posting

[Malissa]

I think I need to start over from the beginning as I am new to this. Joining
up to different parts of code isn't going well here, so I will explain
eveerything that is supposed to happen here.

My spreadsheet starts out with 5 columns of info:

A B C DD E
A B C D
A B C D
A B C D E
A B C D E
A B C D E
A B C D
A B C D E

What I want to do is insert a new column before 'A' to make a new 'A' and
the stuff in the original 'A' column will move to 'B'. Then I want to take
new cell 'D1'and copy/paste the value from that cell in all cells that are in
column 'A' that have values next to them in column'B'.

A B C DD E F
DD A B C D E
DD A B C D
DD A B C D
DD A B C D E
DD A B C D E

The next step would delete row 1, as that information is no longer needed.

DD A B C D E
DD A B C D
DD A B C D
DD A B C D E
DD A B C D E
DD A B C D E

I would then like to take the values in column 'F' and move them under the
last filled cell in column 'C'. There should be no spaces in column 'C', and
there should also be nothing left in column 'F'.

A B C DD E
DD A B C D
DD A B C D
DD A B C D
DD A B C D
DD A B C D
DD A B C D
E
E
E
E


This macro cannot reference any specific cells other then 'D1' as the size
of the worksheet can change.

Thanks,

 

[Per Jessen]

Hi Malissa

Maybe this is what you need:

Sub Manupulate()
Columns(1).Insert
LastRow = Range("B1").End(xlDown).Row
Range("D1").Copy Range("A1", Cells(LastRow, 1))
Rows(1).Delete
Range("F1", Cells(LastRow - 1, "F")).Cut Cells(LastRow, 3)
LastR = Cells(Rows.Count, 3).End(xlUp).Row
FirstR = Cells(1, 3).End(xlDown).Row
For r = LastR To FirstR Step -1
If Cells(r, 3).Value = "" Then Rows(r).Delete
Next
End Sub

Regards,
Per

 

[Malissa]

Thats exactly what I needed! Thank you!