Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a serious problem with a huge Excel sheet which I need to remove blank rows from.

My problem is that I cannot use the classic F5->Select blanks->delete, since some of the fields on not blank rows are also blank.

Simplified example:

enter image description here

In the above example, how would I go about deleting rows 2, 3, 5, 6, 8, 9, 11, 12, 14, 15, 16 without f.x. C7 ending up in C2?

Selecting the blank rows individually would be too time consuming in the real Excel sheet, since it has over 35,000 rows, and this stunt is not a one time thing.

share|improve this question

7 Answers 7

up vote 1 down vote accepted

Here's a VBA solution that will work no matter where the blanks are. Just replace the sheet name in the code with your worksheet name, as noted in the code comments.

Sub delblankrows()

Dim s1 As Worksheet
Dim tmpR As Range
Dim rowcount As Long, colcount As Long, i As Long, j As Long, k As Boolean

'Change "Sheet1" to the name of your worksheet.
Set s1 = Sheets("Sheet1")
Set tmpR = s1.UsedRange
rowcount = tmpR.Rows.Count
colcount = tmpR.Columns.Count

'Starts from bottom row and looks for non-empty cells from left to right.
'Moves to row above if non-empty cell is found.
'If none is found, then deletes row and shifts values up.
For i = rowcount To 1 Step -1
    k = 0
    For j = 1 To colcount
        If tmpR.Value2(i, j) <> "" Then
            k = 1
            Exit For
        End If
    Next j
    If k = 0 Then
        tmpR.Rows(i).Delete Shift:=xlUp
    End If
Next i

End Sub
share|improve this answer
    
Thanks! - I had a friend look at this VBA code and it must have worked because I have a correct sheet in my hands now :) Thanks for the other suggestions also, they would probably work just fine also. –  Casper Sep 28 '11 at 10:35

just select the whole B and C rows together, right-click and select Delete. From the options given, select "Entire column". This will get rid of the empty B and C rows and shift the D (1-3) to appear in B (1-3), just next to row A.

share|improve this answer
    
This will not work for the real sheet, only the simplyfied example I gave above. I will try and give a better a example that better illustrates the complexity of my problem. –  Casper Sep 27 '11 at 12:17

If you have fixed criteria for the deletion, you could write a macro to do it. Look for the columns you need deleted and delete them with (say column B):

columns("B:B").Select
Selection.Delete Shift:=xlToLeft
share|improve this answer

I very often use steps below to remove blank rows. You must do it before formatting cells.

  • select all in Excel a sheet
  • copy
  • open Notepad
  • paste in notepad (it will take only "clean data", without formatting)
  • select all
  • copy
  • open Word
  • paste in Word
  • replace all double paragraph marks with single ones (from ^p^p to ^p)
  • repeat above step until replacing all double paragraph mark occurrences
  • select all
  • copy
  • open Excel
  • paste in Excel
share|improve this answer
    
I don't know why Microsoft did not developed a smart solution for this recurrent work... –  kokbira Sep 27 '11 at 13:52

A pivot table with the same layout would do it, and fast. If you also need to preserve the original order of the rows (minus the blanks), use an additional column, which I've called "ForSort". The formula in cell D2 is =IF(ISBLANK(A2),"",ROW(A2)) . (Copy that formula all the way down column D.)

enter image description here

Put all the fields in the "Row Labels" section of the "Pivot Table Field List" form, as shown above. There's no need for anything in any other section.

You can then select the entire pivot table, and copy/paste-special, values to preserve the pivot table layout.

Finally, you can do a "Replace" to get rid of the "(blank)". And delete the "ForSort" column from the Pivot table copy if you no longer need that.

share|improve this answer

You can insert a helper column before column A in row 2 with the formula:

=COUNTA(B2:XFD2)

Copy it down 35,000 rows, autofilter column A for 0's, highlight the visible rows, and delete them. Unfilter and the rows that had a value in at least one column will remain. Then delete the helper column.

share|improve this answer
    
that's nice and clean; wish I'd said that! –  F106dart Sep 28 '11 at 17:22

Simply find the unique column. Filter it with "Blank" value and delete all rows from excel. Then remove filter and you will get only non-blank rows

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.