Delete Empty Rows

  • Morning all,


    I have read all the tutorials and examples of how to delete rows IF the row contains no data within a worksheet or workbook.


    However .. I don't want all rows deleted, just rows within a set range.
    I can't find any reference to deleting blank rows within a range, just the entire workbook or worksheet.


    Can anyone help?

    Tayler [the blue fox] :thanx:

  • Re: Deleting Blank Rows Within Set Range


    Use something like this syntax

    Code
    1. Sub DeleteSomeRows()
    2. Dim r As Long
    3. For r = 100 To 55 Step -1
    4. If 'Your criteria are True Then
    5. Rows(r).Delete
    6. End If
    7. Next r
    8. End Sub


    Where the range r loops specifies the rows (or columns) you want to check. Use Cells(r, column) to check deletion criteria in various cells in each r row as you get to it.

  • Re: Deleting Blank Rows Within Set Range


    I should have read more closely, if you specifically want to delete blank rows in a range (say rows 55 to 100) then use

    Code
    1. Sub DeleteSomeRows()
    2. Dim r As Long
    3. Application.ScreenUpdating = False
    4. For r = 100 To 55 Step -1
    5. If WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, Columns.Count))) = 0 Then
    6. Rows(r).Delete
    7. End If
    8. Next r
    9. Application.ScreenUpdating = True
    10. End Sub
  • Re: Deleting Blank Rows Within Set Range


    umm .. not sure what I did wrong .. but that kinda did nothing.


    To explain a little more ..
    I have 12 sheets that collect data. I then have a macro to copy all 12 sheets onto a main data page.
    Once this has been done, there are blank rows.


    What I require at the bottom of my code .. is to search between ROW 5:10804 for any blank rows and delete them .. then sort the data descendingly from Column B.



    Here is my code so far.



    Hope this makes sense

    Tayler [the blue fox] :thanx:

  • Re: Deleting Blank Rows Within Set Range


    Code
    1. Range("A50:A100").SpecialCells(xlBlanks).EntireRow.Delete


    Assumes that if a cell in column A is blank then the row is blank. Typically there's at least one field that must have an entry for each record, so I'm assuming that applies to your situation, else I'd have to add a couple more lines to sniff the row for entries.


    But I'd probably first look for a way to modify the copy/paste routine to just exclude the blanks to begin with.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Deleting Blank Rows Within Set Range


    sorry to be difficult .. but not aure where in my code that line has to go.
    Tried it in a couple of places and it throws an error.


    Can you suggest a code to remove the blank lines b4 they copy into the main sheet?


    Thanks

    Tayler [the blue fox] :thanx:

  • Re: Delete Empty Rows


    I have sorted .. but want sort Descending .. not Ascending.


    If sorted B5:AK10804 with Column B Descending .. the blank lines appear first ..


    Hence wanting to delete blank rows ranged between B5:B10804


    Hope this makes sense.

    Tayler [the blue fox] :thanx:

  • Re: Deleting Blank Rows Within Set Range


    Quote from [the blue fox]

    sorry to be difficult .. but not aure where in my code that line has to go.
    Tried it in a couple of places and it throws an error.


    Can you suggest a code to remove the blank lines b4 they copy into the main sheet?


    Thanks


    I would think that just before END SUB would be a good enough place for the one-liner to kill the blank rows. If it doesn't find any blanks it will throw an error. Maybe stick an "ON ERROR RESUME NEXT" just before it?


    The code to copy without the blanks would involve the specialcells method to select just the rows that have formulas and/or values and then you'd either have to paste to the next available cell or insert-paste the copied values.


    Just stick to the one-liner for now, the other suggestion maybe something to ponder down the road...

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Deleting Blank Rows Within Set Range


    Quote

    I have sorted .. but want sort Descending .. not Ascending.

    Who said anything about the sort order?



    Quote

    If sorted B5:AK10804 with Column B Descending .. the blank lines appear first ..

    Then they aren't really blank.

  • Re: Deleting Blank Rows Within Set Range


    Quote from Dave Hawley

    Then they aren't really blank.


    :roll: :lol:

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Delete Empty Rows


    Not laughing at you Dave...


    Just chuckling over the notion that we've been looking for answers to deleting blank rows and you rightly point out they haven't been blank. :p


    I was wanting to quote the whole part of your response with the nested quote, but for some reason when I quote you and you've quoted someone else, it wipes the someone elses quote within your quote?

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]