Macro to delete row if the first characters are numbers

Hi everyone. This is my first post.

I am new to writing VBA macros. I am trying to find a way to remove rows that start with three numbers and then a space. I need the macro to perform this only for the selected row and afterwards jump to the next one (this is because sometimes I want to keep the row even if it starts with three numbers and a space)

07373 Hollolan peruskoulun yläaste
Terveystie 8 ja 10
15870 Hollola
138 Peruskoulun erityiskoulut
07385 Kankaan koulu
Opintie 3
15870 Hollola
162 Lukiot

Thanks in advance!

Thanks for your prompt response nathan! How do I fuse those bits into a working code? Yes, I'm that new to this .

nathansav ,
wouldn't this delete also numbers with more than 3 digits?

because 3rd digit is number also at 99999

Hi..

Try this.. see attached file to show it working..

Please Login or Register  to view this content.

@ nathansav

I am pretty sure the OP wants to iterate through a column of strings and give him the option to delete the ones that have 3 digits followed by a space at the beginning..

Then again.. I could be wrong..

Thanks apo. Your code does exactly what I requested! Is it possible to make it work with a macro shortcut instead of a button? Also how can I get the button to show in my own worksheet? I tried copying your code to the module of VBA.

Put the following in a Module.

Note: Change the name of the sheet if needed..

Please Login or Register  to view this content.

Then Click "Macros" on the Developer Tab (Enable this if it isn't already)... Select the "Delete" macro and then Click the Options Button..

You will then see a section where you can set a Shortcut to run the Macro..

Thanks apo!

See my previous post..

Then Click "Macros" on the Developer Tab (Enable this if it isn't already)... Select the "Delete" macro and then Click the Options Button..

You will then see a section where you can set a Shortcut to run the Macro..

lol.. that's twice in one thread that someone has edited their post and made me look like I am talking to myself..

I am not going mad.. I swear..

Please Login or Register  to view this content.

Apo: your code works like charm, except I would like it run in whichever sheet is active instead of Sheet1. Also, sorry for causing confusion with post editing.

Change to:

Please Login or Register  to view this content.

Originally Posted by Ilwuen

I need the macro to perform this only for the selected row and afterwards jump to the next one (this is because sometimes I want to keep the row even if it starts with three numbers and a space)

Please Login or Register  to view this content.

Note:
Deleting rows/columns with For Each loop will fail if you are deleting consecutive rows/columns.

test test test

Note:
Deleting rows/columns with For Each loop will fail if you are deleting consecutive rows/columns.

Thanks for pointing that out Jindon.

@ Ilwuen.. If you want to use my code you will have to change the For Each loop to a For Next loop and step backwards (similar to what Jindon has done)..

If stepping through the rows from bottom of list to top fees a bit 'un-natural'... you could use my existing code and instead of deleting each row as you go.. form a range to delete using Union method and delete them all in one go at the end..

Like:

Please Login or Register  to view this content.