Removing empty rows with a macro

**Mikie** · 10-10-2009, 05:49 AM

Hi,

I want to create a macro that will cut and paste a worksheet out of an existing workbook and tidy up the table by removing all rows where there is no value in one of the fields (product or part number for example).

Is this possible please.

Thanks,
Mike

**broro183** · 10-10-2009, 06:30 AM

hi Mike,

Welcome to the Forum

Yes, this is definitely possible.

Have you tried searching the Forum for "delete rows" before posting your question?
(I know there a lot of threads returned for this search.)

In case you don't find anything suitable (?), here is a google search that shows a range of possibilities: http://www.google.co.uk/search?hl=en...G=Search&meta=
The dmcritchie, J-Walk & Ozgrid links are all worth a look.

If you would like more specific help, can you please upload a sample workbook with your existing layout?
It may also be useful if you could record a macro of your actions [alt + T + M + R] when doing this manually. We can then modify this code in the uploaded workbook to make it more flexible.

hth
Rob

**Mikie** · 10-10-2009, 12:54 PM

Hi Rob,

Thanks for your help. I found the macro below, but being a novice have not got it to work.

Directly below is manually what I want to achieve, but I want the auto macro to search for empty rows.
Thanks,
Mike

Please Login or Register  to view this content.

=====================

Please Login or Register  to view this content.

**shg** · 10-10-2009, 01:13 PM

Mikie, please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

**broro183** · 10-11-2009, 03:15 PM

hi Mikie,

To put code tags around your code, go to Edit & type "[ code ]" (without the appostrophes or spaces) before the start of your code & type "[ / code ]" (without the appostrophes or spaces) at the end of your code.

When you are creating (& maybe editing) a post using the Advanced Post option (rather than the Quick Reply) you can select the relevant text & press the "#" icon above the message window to wrap your code with code tags.

Once the post has the code tags fixed let me know & I'll post a solution for you

hth
Rob

**Mikie** · 10-11-2009, 04:40 PM

Hi Rob,

Thanks for the direction. Hopefully I've done this correctly.

Mike

**broro183** · 10-11-2009, 05:35 PM

hi Mike,

Yes, I think you've got it right - it would still be a good idea to read the rules though ;-)

The reason the macro didn't work for you is because the as the Help files state, the CountA function...

Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
Syntax
COUNTA(value1,value2,...)
Value1, value2, ... are 1 to 255 arguments representing the values you want to count.
Remarks
A value is any type of information, including error values and empty text (""). A value does not include empty cells.

I have added a macro that creates a copy of the sheet, names each "table" range, & then loops through these named ranges looking for & deleting the rows with the blank cells. I haven't tested it properly because I haven't fixed the formulae links which broke when I opened it in Excel 2007 (formula string length was >255 characters). Note - you'll need to fix the formulae yourself.

goodluck

hth
Rob

**Mikie** · 10-12-2009, 01:06 PM

Hi Rob,

Thats fantastic - thanks for all your help. I've put it into my main workbook and it does the the job great. I do need to make a few alterations for it to do exactly what I need. I want to assign some macros to a few buttons which I can do, then I'd like to be able to do the deletes within the worksheet itself (I'll then cut and paste the results into a word document) + an undo macro so I can do the same thing with different data. I also like to do the same thing you did, but on a different workbook rather than worksheet, so I can email it on a small file to someone.

Can you recommed a good book for learning this kind of thing so that I can be less reliant on experts such as yourself ?

Many thanks once again for this

Mike

**broro183** · 10-12-2009, 06:15 PM

hi Mike,

I'm pleased I could help - thanks for the feedback

So you'd like a book to read - cool, the VBA bug may be catching

I'm still learning too & not an expert (here's some of them - http://www.mvps.org/links.html#Excel). I find that reading through threads on this & other Forums is very useful & have read a few books. John Walkenbach's Power Programming titles are a reasonably easy read (& may open your mind to other possibilities) & I'd also recommend hunting for & downloading "Spreadsheet Modelling Best Practice" by Nick Read and Jonathan Batson for general design suggestions.

Stan has made some suggestions here:
http://www.excelforum.com/excel-gene...-in-excel.html

The below link has some more reading, but as my signature says I think the best way to learn is to experience, so play with the macro recorder & then review/breakdown the recorded code to increase your knowledge.

http://www.excelforum.com/excel-misc...additions.html

Recording macros of your actions will give you the basis of your actions for your next projects & Ron's below pages should help with your email task.
http://www.rondebruin.nl/sendmail.htm
http://msdn.microsoft.com/en-us/library/bb268022.aspx
(this may be a copy of the first one - I just found it & haven't read it.)

If you're happy with the solution, can you please mark the thread as solved (instructions are in the Forum Rules)?
Feel free to add to my reputation by clicking on the blue scales at the top right of one of my posts - it is appreciated.

hth
Rob