I'd make the follow changes. First, I would use Intersect with the UsedRange
in case the user selected an entrire colum to scan.

Set myRng = Application.Intersect(Selection.Worksheet.UsedRang e, _
Selection.Areas(1).EntireRow.Columns(1))

Also, I would add code for the case in which a cell in the contains only a
single apostrophe.

Dim ColNdx As Long
Dim EmptyRow As Boolean

For r = LastRow To FirstRow Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
Else
ColNdx = 1
EmptyRow = True
Do Until ColNdx = Columns.Count
If (myRng.Worksheet.Cells(r, ColNdx).HasFormula) Or _
(myRng.Worksheet.Cells(r, ColNdx).Value < vbNullString)
Then
EmptyRow = False
Exit Do
End If
ColNdx = myRng.Cells(r, ColNdx).End(xlToRight).Column
Loop
If EmptyRow = True Then
Rows(r).Delete
End If
End If
Next r


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Dave Peterson" wrote in message
...
Option Explicit
Sub DeleteEmptyRowsInSelectionIfWholeRowIsEmpty()
Dim FirstRow As Long
Dim LastRow As Long
Dim r As Long
Dim myRng As Range

Set myRng = Selection.Areas(1).EntireRow.Columns(1)

With myRng
FirstRow = .Row
LastRow = .Rows(.Rows.Count).Row
End With

Application.ScreenUpdating = False
For r = LastRow To FirstRow Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub

Based on one cell:

Option Explicit
Sub DeleteEmptyRowsInSelectionIfCellIsEmpty()
On Error Resume Next
Selection.Areas(1).Columns(1).Cells.SpecialCells(x lCellTypeBlanks) _
.EntireRow.Delete
On Error GoTo 0
End Sub

DavidHawes wrote:

Just wanted to move this up the forums as my subsequent question (below)
would be lost otherwise...

---------------------

As an extra, if I wanted to specify a range of cells that, if blank,
would
result in the deletion of the rows where those cells lie, how would I do
this?

Thanks again.

David

"Gord Dibben" wrote:

David

Try this macro to delete just empty rows.

Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub


Gord Dibben MS Excel MVP

On Fri, 23 Feb 2007 05:33:29 -0800, DavidHawes
wrote:

Hi,

I've set up a macro which re-organises an excel spreadsheet into a
format
that enables me to import the data contained within it directly into
an
Access database i've set up.

This works perfectly.

However, I want the macro to delete out any lines of my table (which
is
fixed in size) that do not contain any data.

Is this possible? If so, can someone please explain what code I need
to
enter to get my macro to do this?

Many thanks,

David :-)



--

Dave Peterson