Removing blank rows
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Removing blank rows

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    My spreadsheet has 2 columns of data. Column A contains part numbers and column B contains serial numbers. I frequently delete part numbers (and serial numbers) due to part discontinuations, etc which leaves many blank rows within my spreadsheet. Removing these rows individually can get time consuming (I sometimes have 40 or 50 rows of data sparatically located throughout my spreadsheet).

    How can i remove the blank rows all at once?

    Thx,
    Noir

  2. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try sorting the columns

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    this works well...........

    Sub DeleteEmptyRows()
    LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
    If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
    Then Rows(r).Delete
    Next r
    End Sub

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Eddie,
    Sorry but, i could not get your code to work. Please help.

    Noir

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Philippines
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Noir,
    someone send me a little add-in that does just what you need.
    Give me your e-mail, I'll send it to you
    Hans

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Friend,

    why dont you sort the rows...

    you will have all your blank rows at the end.

    ni****h desai
    http://www.pexcel.com

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    Seriously I'd go with Nisht and Brian's recommendations- quick and easy. If the original order of the list is needed you could add a column of sequential numbers before sorting - then resort the list by the new column after deleting the rows with no data.

    Hope that helps,
    Adam

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks for your help guys!! The sorting and the codes worked.

    Noir

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com