Problem with deleting blank rows or last row

[Alan]

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).EntireRow.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

 

[Jim Cone]

You cannot remove rows from an Excel worksheet.
When you delete a row it is replaced with a new blank row.
You can hide rows and give the appearance of rows being removed.
Hidden rows are still part of the sheet and still contain whatever data/formatting was
assigned to them.
And the hidden rows may or may not interact with any formulas/macros you might create.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)





"Alan" <[email protected]>
wrote in message

 

[Alan]

Jim,
What is the best way to avoid iterating through them, to
examine data in all non-blank rows?

This is what I am really after. Thanks, Alan

 

[Rick Rothstein]

What is the best way to avoid iterating through

them, to examine data in all non-blank rows?

This is what I am really after.

You should always ask for what you actually want (do not try to simplify
your questions for us, that will only get you working solution for a
question you do not actually care about). The following code will create a
range of rows, and set it to the DataRows range variable, where each row in
DataRows will contain at least one piece of data (note, that is data,
constants, NOT formulas) somewhere along the row...

Dim LastRow As Long, Lastcolumn As Long, DataRows As Range
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows,
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Lastcolumn = Cells.Find(What:="*", SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
Intersect(Cells.SpecialCells(xlCellTypeConstants).EntireRow,
Columns(Lastcolumn + 1)).Value = "X"
Set DataRows = Columns(Lastcolumn +
1).SpecialCells(xlCellTypeConstants).EntireRow
Columns(Lastcolumn + 1).Delete

If you need to locate both data and/or formulas, let me know and I'll modify
the coded for you.

Rick Rothstein (MVP - Excel)

 

[Alan]

Rick,
Thanks. I`ll try this out. Alan