Solved

Unable to "Delete Blank Rows"

Posted on 2014-03-10
18
1,163 Views
Last Modified: 2014-03-11
Hi All,

I know this gets asked a lot, so here is what I've tried...

1. Select Sheet by clicking on the corner button to the left of 1 and above A
2. Press F5
3. Select Special
4. Select Blanks
5. Click on Home Ribbon > Cells > Delete Sheet Rows

Then I repeat steps from 1 to 3 and confirm the blanks have not been deleted.
Frustrated, I select the blank rows manually (using the thing on the left to select the entire row).  I press the clear formatting button on the Home Tab > Editing Group.

Then I repeat steps from 1 to 3 and confirm the blanks have not been deleted. More frustruated I select every cell to right of my data and delete all the columns.  Then I do the same for every row below my data.

Then I repeat steps from 1 to 3 and confirm the blanks are still not deleted.

Please, can someone tell me what I can do to quickly delete the 'so-called' blank cells?

Is there something hidden that I don't know about on the worksheet?  I have many sheets where this is an issue.  

So far I have found 1 tedious solution that works, I need to copy my data range to a new sheet in a new workbook then select the whole sheet and copy it back over my original range.  There must be a better way!!!

Pls let me know if you've seen this before or have any ideas,

Cheers,
0
Comment
Question by:DrTribos
  • 8
  • 6
  • 3
  • +1
18 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917388
it worked just perfectly for me (as expected), so please attach the file so we can see why it does not work on your file ...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917389
I guess/presume the worksheet is "protected" or so....
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39917437
Here you will find several macros to delete empty rows.

Sub DeleteBlankRows1()

'Deletes the entire row within the selection if the ENTIRE row contains no data.

'We use Long in case they have over 32,767 rows selected.

    Dim i As Long

    'We turn off calculation and screenupdating to speed up the macro.

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False

        'We work backwards because we are deleting rows.
        For i = Selection.Rows.Count To 1 Step -1
            If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
                Selection.Rows(i).EntireRow.Delete
            End If
        Next i

        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Sub DeleteBlankRows2()

'Deletes the entire row within the selection if some of the cells WITHIN THE SELECTION contain no data.

    On Error Resume Next

    Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

    On Error GoTo 0

End Sub

Sub DeleteBlankRows3()

'Deletes the entire row within the selection if the ENTIRE row contains no data.

    Dim Rw As Range

    If WorksheetFunction.CountA(Selection) = 0 Then
        MsgBox "No data found", vbOKOnly, "OzGrid.com"
        Exit Sub
    End If

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False

        Selection.SpecialCells(xlCellTypeBlanks).Select

        For Each Rw In Selection.Rows
            If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
                Selection.EntireRow.Delete
            End If
        Next Rw

        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Sub MoveBlankRowsToBottom()

'Assumes the list has a heading

    With Selection
        .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, _
              Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With

End Sub

Open in new window

0
 
LVL 14

Author Comment

by:DrTribos
ID: 39917442
Thanks for the VBA, I have seen lots of those.  I've been trying to do it manually... I think there is an issue with my worksheet / workbook.

I'm yet to try a macro for this... I'm just annoyed that my sheet (attached) appears to be misbehaving.

I have removed all sensitive data, well overtyped it, will be interesting to see if the problem persists.  My testing on the upload file suggests it contains the problem...
drTribos.xlsx
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39917447
Bah - I just reopened my dest doc and the blanks are, well proper blanks... I don't know.
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39917450
If you press End and Home keys, (first End then Home) where does your cursor end up?

If the cursor goes to the bottom right of your known data then you have cleared the surplus rows and/or columns.

The excel sheet will always show 1048576 rows and 16384 columns but they aren't necessarily in use. If you attempt to hide them, the system will see them as being used so will count them as part of the Used Range.

Otherwise, go to the column one column right of your known data and select the whole column by clicking the column letter header, press shift and End Right. Press Alt E A A, to clear everything; contents and formatting.

Repeat for rows below your known data, replacing End Right with End Down.

Press Home or Ctrl  Home to get back to the top of the sheet and save. This should reset the Used Range. Now press End Home again and it should go to bottom right of your data.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39917458
The fact that you have re-opened your test document, did you save it when closing it? I suspect that has reset the Used Range as per my comment above.

Thanks
Rob H
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917460
the file you attached worked just fine for me, so I must assume that it's an issue on your PC, so either the excel is somewhat corrupt, or some third-party add-in that blocks the feature somehow. what add-ins do you have? can you de-activate them (temporarily) to see if that would solve the issue?
can you try the operation on another PC?
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39917474
Rob,

End > Home takes me to H281, which incidentally is my set of blank cells.  The used range should be B185.

I followed your instructions, please tell me if my interpretation is incorrect (for the stated range A1:B185... select 'column-C' by pressing "C" in the header.  Select everythign to the right. Press Alt E A A (sequentially, obviously), repeat at row 282 for everything belown my data.

Press End > Home.  Cursor is right back at H281  :-(
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 14

Author Comment

by:DrTribos
ID: 39917497
Hi Guy,

Yes - annoying right.  I tested the file before and after upload... it exhibited the same behaviour prior to upload then after upload worked perfectly (i.e.  save > close > open > test (confirm issue)  > close > upload > open (locally) > test (issue gone)...).

There is an addin that I did not know about... LoadTest - not sure what it is / why I have it... I need to elevate privs to remove :-/
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39917518
You missed out going back to the top and the Save!!

For the rows part start at row 186 if 186 to 281 are supposed to be blank.

Thanks
Rob H
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39917543
Thank you Rob, Guy & MacroShadow

Rob's solution was the one that I wanted... usually I'd resort to VBA but not this time.  Thanks for all the input.

Time for sleep.  Cheers,
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39917562
Glad to be of assistance.

I come across this type of problem fairly often, where people have selected the whole row/column to highlight in colour. Or have selected the whole sheet and coloured white to get rid of gridlines rather than switching them off on View tab.

Thanks
Rob H
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39919274
Hi Rob,

The strange thing is that this particular workbook was created to replace an ini file solution in a VBA project.  To the best of my memory the only things that I have done are entered text - no formatting, except cell wrapping & row height.

BUT the annoying thing is that a bug like this (and a bug it is) takes so long to figure out.  Why should a user have to save a document / spread sheet for their changes to be applied.  You don't have to answer that.

Thing is... I get the formatting angle, I just don't think that's what happened.  Much glad that it's fixed now.

Cheers,
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39920268
Even row height and column width are counted as formatting. If its not the default setting, it has been used!!

Cheers
Rob H
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39920477
Hmmmm.... ok.  But why do I have to save to apply my changes that is soooo 2003!

You don't have to answer that ;-)
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39920595
I guess Saving clears the Undo history and any cached data.
0
 
LVL 14

Author Comment

by:DrTribos
ID: 39921841
I guess it would be possible to test further by using VBA to clear the Undo history (I believe there are indirect methods to do this), not sure if there is a direct / indirect way to clear cache.

However, for now I can live with save.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now