Monday, August 15, 2005

Deleting empty rows


Here's the answer I wrote to a question posted on the JMT forums.

Question
I have a lot of entries in a spread sheet.
each one is separated by an empty row (sometimes 2 rows are empty)
I was wanting to activate cell a4 and then run the macro to see
if that cell is empty. Runs until it hits a date ("08/10/06")
I've seen it before on an old excel help screen,
but cant remember it exctly. Thanks for your help.
Answer

1 Create this function to find the last row on the sheet by copying and pasting into a module in the Projefct Explorer (shortcut ALT F11)
Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%
' Error-handling is here in case there is not any data in the worksheet
On Error Resume Next
With ws
' Find the last real row
LastRow& = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
' Find the last real column
LastCol% = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
End With
' Finally, initialize a Range object variable for the last populated row.
Set LastCell = ws.Cells(LastRow&, LastCol%)
End Function
2 Then create this procedure by copying and pasting into the module below the function.
Sub delrows()
lastrowno = LastCell(ActiveSheet).Row
Range("A1").Select
Do While ActiveCell.Row < lastrowno
If ActiveCell = "" Then
ActiveCell.EntireRow.Delete
lastrowno = lastrowno - 1
Else
If DateValue(ActiveCell) > DateValue("11/8/2005") Then Exit Sub 'enter date here
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub


Posted by: Jethro | 9:19 PM |