Macro to copy and paste special values for column data and filter column data

Hello everyone,

I am using Excel 2010:

Update: I have, through much trial and error figured out how to resolve all of my issues below except for how to create a macro to filter column data. In attempting to record the macro to filter the column, I keep receiving the error message as follows: "Too many line continuations". Can anyone help with this?

My issue is that I have the attached spreadsheet where a tab "DISTRIBUTE" contains data that is pulled from other tabs within the workbook by means of VLOOKUP. There are 2000 rows of Vlookup formulas to pull data.

I'm trying to create a macro that will manipulate column data within the "DISTRIBUTE" tab as follows:

1.) Copy concatenated phone numbers, ex:0123456789, from column "P" (the formulas are active) and paste/special value them into column "Q". Then format the phone numbers, ex: (012) 345-6789.

2.) Filter out column "A" data, "0" and "(Blanks)"

3.) Filter out column "B" data, anything that is NOT "0" and "(Blanks)"

4.) Sort the entire worksheet by column "J", ascending, and hide columns, "B", "G", "J", "N", "O", "P". - This I have been able to do successfully as a separate macro.

I have tried solutions for other posters from this site as well as the web, all of which have not worked for my particular situation.

I have tried both recording and writing the macro but neither have worked for me.

My goal is to create a single macro that when launched will accomplish all of the above. Is this even possible?

Thank you,

any help would be greatly appreciated!

Hi ascottbag
When you say

Filter out column "A" data, "0" and "(Blanks)"

and

Filter out column "B" data, anything that is NOT "0" and "(Blanks)"

do you mean "Delete those rows"?

Hi ascottbag
"Gotta be honest with you...not sure what you're looking for. Based on my understanding, see if this does it for you

Please Login or Register  to view this content.

Hi ascottbag
Try this code

Please Login or Register  to view this content.

Hi ascottbag
You're welcome...glad I could help.

Either of these will do it

Please Login or Register  to view this content.

Please Login or Register  to view this content.

My pleasure.