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 :-)

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 :-)

Many thanks - that is great.

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 :-)

Sub tester()
Dim C As Range
With Range("C4:C23") ' "C4:E23"
Do
Set C = .Find("", LookIn:=xlValues, LookAt:=xlWhole, _
MatchCase:=False)
If C Is Nothing Then Exit Do
C.EntireRow.Delete
Loop
End With
End Sub


Gord

On Mon, 26 Feb 2007 01:01:08 -0800, DavidHawes
wrote:

Many thanks - that is great.

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 :-)