Macros Coding Help Please.

[abbeyb]

Hello.

I have created a spreadsheet for work which will save a considerable amount of time.

Currently there is one report that I need to clean manually before pasting into the spreadsheet and I was hoping to create a macro so that the report can be cleaned at the click of a button.
I have experience with Excel but not Macro's/VBAs so this is where I have come stuck. I was hoping if I write the steps I do manually then someone will be able to assist me with turning it into code.

Manual Process
1. Select Column B from B5 to end of last text [I have Range("B5").End(xlDown).Select for this]
2. Select Go To Special > Constants > Text > Ok
3. (All the names will be highlighted) = < Ctrl OK (to copy cell to the left)
4. Select Column B, Copy & Paste Values
5. Select Column B from B5 to end of last text
6. Select Go To Special > Blanks > Ok
7. (All blank cells are highlighted) = *arrow up* Ctrl OK (to copy cells above)
8. Select Column B, Copy and Paste Values
9. Column I, Go To Special>Blanks> Ok
10. Delete Rows
11. Column H, Find and Replace> Find> *TEXT A*
12. Select all in find box > Right Click >Delete > Delete Entire Row
Repeat steps 11 and 12 various times with different text
13. Delete Columns A, C, D & E


Any help would be greatly appreciated.

 

[RobP]

Hi Welcome to the forum.

As you have your process so well written as instructions - it should be quite an easy task for you to switch on the "Macro Recorder" and go the steps yourself 1 at a time. Then switch off the MAcro recorder.

The result will be just what you want ?

cheers
Rob

 

[abbeyb]

Thank you for your reply. I have tried this but for some reason it doesn't work and when I push the button, the first two columns turn to #REF!

 

[RobP]

Hi,

Can you step through the macro (eg, instead of hitting run, put your cursor on the first line of code, and hit F8, line by line. If might give some idea where it is failing ?