Removing empty rows with a macro

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

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

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

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/10/2009 by mike.diss
'

'
    Rows("40:104").Select
    Selection.Delete Shift:=xlUp
    Rows("48:58").Select
    Selection.Delete Shift:=xlUp
    Rows("54:66").Select
    Selection.Delete Shift:=xlUp
End Sub

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

Sub DeleteBlankRows1()
Range("39:104").Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.



'We use Long in case they have over 32,767 rows selected.

Dim i As Long



    'We turn off calculation and screenupdating to speed up the macro.

    With Application

        .Calculation = xlCalculationManual

        .ScreenUpdating = False

      

    'We work backwards because we are deleting rows.

    For i = Selection.Rows.Count To 1 Step -1

        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

            Selection.Rows(i).EntireRow.Delete

        End If

    Next i



        .Calculation = xlCalculationAutomatic

        .ScreenUpdating = True

    End With

End Sub

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

Thanks.

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

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

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