Hi. I am using Excel 2007 and am having a problem deleting blank rows
or finding the last row.

When I am in the spreadsheet and press Ctrl-End, it goes to row 660.
However, when I get the number of rows for the ActiveSheet
(ActiveSheet.rows.count), I always get 1,048,576. I tried all sorts
of ways to delete blank rows (blank in Column A) --- see below --- but
I still always have 1,048,576 using ActiveSheet.rows.count.

Any ideas on why this occurs? Alan

Sub DeleteBlankRows()
Dim row As Long, i As Long, LastRow As Long, content As String
Dim WS As Worksheet
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
For Each WS In ActiveWorkbook.Worksheets
LastRow = WS.Rows.Count
Debug.Print WS.Name & ": " & LastRow & " rows"
'Deletes the entire row within the selection if the ENTIRE row
contains no data.
'We turn off calculation and screenupdating to speed up the
macro.
For row = LastRow To LastRow - 5 '1 Step -1
content = Trim(WS.Cells(row, 1).Value)
If Len(content) = 0 Then
' EntireRow.Delete
'Debug.Print " Deleted row " & row
End If
Next row
Debug.Print " " & LastRow & " rows"
Next WS
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
Public Function GetLastRow(ByVal rngToCheck As Range) As Long

Dim rngLast As Range

Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByRows,
searchdirection:=xlPrevious)

If rngLast Is Nothing Then
GetLastRow = rngToCheck.row
Else
GetLastRow = rngLast.row
End If

End Function

Sub DeleteBlankRows1111()
Dim lngLastRow As Long
Dim rngToCheck As Range

Application.ScreenUpdating = False

With ActiveSheet
Debug.Print .Name & " has " & .Rows.Count & " rows"
'if the sheet is empty then exit...
If Application.WorksheetFunction.CountA(.Cells) 0 Then

'find the last row in the worksheet
lngLastRow = GetLastRow(.Cells)

Set rngToCheck = .Range(.Cells(1, 1), .Cells(lngLastRow,
1))

If rngToCheck.Count 1 Then
'if there are no blank cells then there will be an
error
On Error Resume Next

rngToCheck.SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
On Error GoTo 0
Else
If VBA.IsEmpty(rngToCheck) Then
rngToCheck.EntireRow.Delete
End If
End If
Debug.Print .Rows.Count & " rows"
End With

Application.ScreenUpdating = True

End Sub

Sub DeleteBlankARows()
Dim r As Long
Debug.Print ActiveSheet.Rows.Count
For r = Cells(Rows.Count, 1).End(xlUp).row To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
Debug.Print ActiveSheet.Rows.Count
End Sub