Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blank Rows (Excel XP)

    I am looking for a way to identify blank rows and delete them in a sheet. I did a search on Blank Rows and did not see anything that looked like what I want to do. I looked at Go To Special and did not see a way to select the blank rows only a row comparison.

    Carla

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

    Re: Blank Rows (Excel XP)

    Perhaps you can sort the sheet on the appropriate column(s)? Blanks will always be sorted at the bottom, which has the same effect as deleting them.

  3. #3
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (Excel XP)

    The sheet gets data from another data source and the data is not in a very usable format. I an trying to create a macro that will clean up and format the sheet after an import. I have identified how to delete the numbers that do not need to be there, how to split a text column of data to leave the numbers I need for a grand total, how to create the formulas, now I am looking for a way to identify what rows are left completly blank in the dataset and delete them. This is just a part of a larger process, so sorting does not seem to work with the multiple columns.

    Carla

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Blank Rows (Excel XP)

    See Hans <post#=458,048>post 458,048</post#>.

    (I'm surprised you didn't find this in your search.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (Excel XP)

    That is exactly what I was looking for. I might not have gone back far enough. Thank you very much.
    Carla

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

    Re: Blank Rows (Excel XP)

    Try this procedure:

    Sub DeleteBlankRows(Optional WorksheetName As Variant)
    Dim WS As Worksheet
    Dim LastRow As Long
    Dim RowNum As Long

    If IsMissing(WorksheetName) = True Then
    Set WS = ActiveSheet
    Else
    On Error Resume Next
    Set WS = ActiveWorkbook.Worksheets(WorksheetName)
    If Err Then
    ' Invalid worksheet name.
    Exit Sub
    End If
    On Error GoTo 0
    End If

    If Application.WorksheetFunction.CountA(WS.UsedRange. Cells) = 0 Then
    ' Worksheet is blank. Get Out.
    Exit Sub
    End If

    ' Find the last used row
    LastRow = WS.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    ' Loop bacvkwards
    For RowNum = LastRow To 1 Step -1
    ' If there are no non-blank cells...
    If Application.WorksheetFunction.CountA(WS.Rows(RowNu m)) = 0 Then
    ' Delete the entire row
    WS.Range("A" & RowNum).EntireRow.Delete
    End If
    Next RowNum
    End Sub

    Call like this to delete blank rows in the active worksheet:

    Call DeleteBlankRows

    and like this to delete blank rows in a sheet named Data:

    Call DeleteBlankRows("Data")

    The code is a much simplified version of the code found in Deleting Rows on Chip Pearson's site.

  7. #7
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank Rows (Excel XP)

    Hans,

    Had to leave earlier today so did not have time to test until this afternoon. I used the code you supplied and I was able to complete a 6 step consolidated macro successfully. Thank you again for the code to complete the process.

    Carla

Posting Permissions

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