Help: Need VBA script to remove rows if a column field doesn't match a certain length.

[rmconard]

So I'm an Excel noob and need, what seems to be, a simple VBA script to do the following:


1) I need to check the length of fields in a column and make sure whatever text is in the field is EXACTLY 10 digits long... if it's more, less or blank then the entire row needs to be removed and all cells shifted up.

2)OPTIONAL (Only need if possible by VBA) - I need to separate a full name into 2 separate columns. So right now I have a single column with a name in it, i.e. FIRSTNAME LASTNAME. I need a script to identify the space between the first and last names and then separate the first and last names. This helps me import leads properly into my ZOHO system. This isn't super important, but would be very useful.


Any help is appreciated guys! I just don't know how to do it ;(

[alansidman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[rmconard]

Welcome to the forum.

we'll be glad to help you but first pls amend your title according rule#1 of our forum.

Sorry, I fixed it. Since my 2nd thing I need help with is optional to me and isn't required, I edited the title to address my main concern.

[Richard Buttrey]

Hi,

First things first. As a self confessed noob I'd be inclined to use standard excel functionality to achieve your aim first. Then when you're happy performing the task manually we can then move on to a VBA macro to do it for you. This is usually the best way to move forward since it's quite ususal when performing the manual process that you realise other steps may need to be taken, and which you might not have thought of if just creating a macro from scratch.

So the first task is to split the names. Add say four helper columns on the left to give you a bit of breathing room. Now copy the name column and paste it into helper column A.

Now select column A and choose the Data TextToColumns icon on the Ribbon (if you have XL 2007 or later) or if you still only have XL2003 on the XL Menu. Use the 'delimited' option and specify the space character as the delimiter and OK

You will now have two columns with the first & last name

In say C1 enter the formula

=LEN(E1)

I'm assuming here that column E is the column that still has the original names.

Now select the whole of your data and autofilter it using column C and choosing not equal to 10. Once the data is filtered seclect all the filtered rows and delete them. Then remove the filter and hey presto.

This has probably taken longer to write than you'll take actually doing it!

[Richard Buttrey]

Sorry Fotis and Alan.

I'd originally missed that one and was about to change it. Which as they are a first timer I'll do now.

Oops. Too late. It's already happened. Not my day

[SiebeBosch]

What about simply selecting the column with names and then choosing "Text to columns" using the "space" as a delimiter?

[Fotis1991]

No worries Richard!

[rmconard]

Hi,

First things first. As a self confessed noob I'd be inclined to use standard excel functionality to achieve your aim first. Then when you're happy performing the task manually we can then move on to a VBA macro to do it for you. This is usually the best way to move forward since it's quite ususal when performing the manual process that you realise other steps may need to be taken, and which you might not have thought of if just creating a macro from scratch.

So the first task is to split the names. Add say four helper columns on the left to give you a bit of breathing room. Now copy the name column and paste it into helper column A.

Now select column A and choose the Data TextToColumns icon on the Ribbon (if you have XL 2007 or later) or if you still only have XL2003 on the XL Menu. Use the 'delimited' option and specify the space character as the delimiter and OK

You will now have two columns with the first & last name

In say C1 enter the formula

=LEN(E1)

I'm assuming here that column E is the column that still has the original names.

Now select the whole of your data and autofilter it using column C and choosing not equal to 10. Once the data is filtered seclect all the filtered rows and delete them. Then remove the filter and hey presto.

This has probably taken longer to write than you'll take actually doing it!

Awesome! Thanks, Richard.


Ok, so the first thing you said worked perfectly. I was able to separate the first and last names, so thank you for that.

However, I'm not understanding your directions on my second problem.

Allow me to elaborate on what I need better... So I have multiple spreadsheets sent to me by my boss that contain lead data for our customers. One of the columns in the spreadsheet is the customer's phone number, however when the spreadsheet was initially generated it was sloppy so some of the fields in the phone number column don't contain phone numbers, or they're blank.

So basically I need to make it so that if a field in the column isn't exactly 10 digits, i.e. 5183355555, then the entire row needs to be removed and all cells need to be shifted up.

Thanks again!

[alansidman]

this vba should do that for you.

Please Login or Register  to view this content.

[rmconard]

this vba should do that for you.

Please Login or Register  to view this content.

Thank you! And thanks again to Richard as well. You guys are awesome and fast!

Both these things worked perfectly and I now have my spreadsheet formatted the way I want.

I tweaked your VBA code just a bit so that I can save the macro and now it asks me what specific column I want to check, because sometimes it's different.

Please Login or Register  to view this content.

Thanks guys!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.