Deleting All True Empty Rows in a worksheet

[decadence]

Hi, I would like to delete all empty rows from a worksheet but the problem I'm having is that when using xl last cell it still finds where the original data used to be. Can someone help with this please

Example

If Row 2 has data in and Row 7 to 15 has data in, then I delete the empty rows I am still left with 15 rows instead of 10 when selecting the last cell using specialcells xllastcell

[Paul_Hossler]

Chip has nice write up and sample macros

http://www.cpearson.com/excel/LastCell.aspx



I'm using 2016, and the line marked 'One doesn't work for me any more (it used to). I have to save the WB and then it works ('Two)

Option Explicit
Sub ResetLastCell()
    Dim x As Long
    
    ActiveSheet.Range("A1:Z20").Value = 1234
    
    MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address
    
    ActiveSheet.Range("A11:Z20").Clear      '   not delete rows
    MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address
    
    
    x = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row  'One
    MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address


    ActiveWorkbook.Save                                     'Two
    MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Address

End Sub

[decadence]

I am using Excel 2007, that code will mess up my workbook if I ran it by putting 1234 values in Range("A1:Z20")

[Paul_Hossler]

The 1234 was just part of my test in a new, empty, throw away workbook to see which technique would work

If one of the techniques work for you, than just incorporate it into your real workbook

If none of the techniques work for you, than just delete my test workbook

[p45cal]

Sub blah()
Dim DelRng As Range
Set myRng = Range("A1", ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count))
myWidth = myRng.Columns.Count
For Each rw In myRng.Rows
  'If Application.CountA(rw) = 0 Then 'or the next line instead.
  If Application.CountBlank(rw) = myWidth Then
    If DelRng Is Nothing Then Set DelRng = rw Else Set DelRng = Union(DelRng, rw)
  End If
Next rw
If Not DelRng Is Nothing Then DelRng.EntireRow.Delete
End Sub