Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blank Rows (2000)

    Does anyone know how to automatically delete blank rows in a spreadsheet?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blank Rows (2000)

    That's a rather broad request. In most worksheets, 99% or more of the 65,536 rows is empty. I assume that you mean empty rows within the "used area" of a worksheet, but have you thought of the consequences? The moment you insert a blank row to enter data in, it will be removed automatically. Seems rather frustrating...

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (2000)

    Can you tell us what you mean by "automatically"?

    Also, can you define what constitutes a "blank row"?

    Do you really want to delete ALL blank rows? If you delete the rows after the last row of data, they will just be put back in.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (2000)

    Linda,
    Assuming, like Hans says, that you want to get rid of some blank rows within your work area, simply sort the rows.

    If the order of the rows is important, and not necessarily alphabetical/numerical/time order, sneak in an index column before you do the anti-row sort. Delete the rows, then resort on the index column.

    Errol

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (2000)

    Sorry I was vague. I run a report in Impromptu and then save it as Excel. There are blank rows in the work area and I was starting to delete them manually. I did what you said - I put in an index column and that worked. Thanks for your help.

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (2000)

    Considering you're having to do this frequently, you might try using this VBA code (which, from memory, should be credited to Legare Coleman):<pre>Public Sub Delblank()
    Dim lLastRow As Long, I As Long, j As Integer
    Dim strWk As String
    lLastRow = Cells(Application.Rows.Count, Columns("A").Column).End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    strWk = ""
    For j = 0 To 25
    strWk = strWk & ActiveSheet.Range("A1").Offset(I, j)
    Next j
    If strWk = "" Then
    Range("A1").Offset(I, 0).EntireRow.Delete
    End If
    Next I
    End Sub</pre>

    HTH
    Gre

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (2000)

    Thanks so much. I tested the code and it works beautifully.

  8. #8
    Lounger
    Join Date
    Dec 2001
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (2000)

    You can also use a filter--with columns selected go to Data/Filter and select auto filter. Select (non-blanks). This filters out all the blank rows. The filter puts a little box at the top right of the column with a down arrow for selection. If you want to turn the filter off, you just toggle auto filter and the blanks are back. I learned this because I was manually deleting blank rows after I copied worksheets into Word.

    The only drawback is you have to make sure you're in the column that is most populated when you choose (nonblanks). I had more data in col A, col B had more blanks as did col C. I was in Col B when I chose nonblanks and it filtered according to what was in Col B. But since it was a filter I turned it off and all my data was back. Then I redid while I was in Col. A and it worked beautifully.

    Nancy Potter

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Blank Rows (2000)

    If you autofilter and select blanks you will only see the blanks in the column and if you select all those rows you can Delete them all-at-once.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •