MACRO HELP

[Guest]

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

 

[Gord Dibben]

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

 

[Guest]

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]

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