If you want to check a range on that row:

Option Explicit
Sub DeleteEmptyRows()
Dim LastRow As Long
Dim r As Long
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Cells(r, "A").Resize(1, 13)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub

This checks for empty cells in A:M in the used range--not formulas that evaluate
to "" or those formulas that have been converted to values.

This line:

Cells(r, "A").Resize(1, 13)
Is the cell in A1, but resized to a single row (1) and 13 columns (A:M).

DavidHawes wrote:

Hi,

Thanks for this, although it's not quite what i'm after and I can't quite
seem to make the code work using the second example you quote...

What I require is for a whole row to be deleted if the cells in specified
rows within my table (in my case A4:M4, A5:M5, A6:M6 etc) are blank.

I tried the following using your example:

On Error Resume Next
Range("A10:M11").Cells.SpecialCells(xlCellTypeBlan ks) _
.EntireRow.Delete
On Error GoTo 0

but this didn't work.

Any help would be gratefully appreciated.

Many thanks,

David Hawes

"Dave Peterson" wrote:

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


--

Dave Peterson