Delete blank rows

[Charlotte Howard]

Hello,
I'm returning some address lines into separate cells, some of which will be
blank.
Is it possible to delete the blank lines using some form of macro?

Cells A1:A6 contain the data. Cell A2 will always be populated, but the
remaining 5 can be blank
Sample
A1 - Address line 1
A2 -
A3 - Town
A4 -
A5 -
A6 - County

In this sample I would like rows 2, 4 & 5 to be deleted, returning
A1 - Address line 1
A3 - Town
A6 - County

Thanks for any help on this one

 

[Gary''s Student]

Try this small macro:

Sub rowkiller()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If IsEmpty(Cells(i, 1)) Then
Rows(i).Delete
End If
Next
End Sub

 

[Charlotte Howard]

Hi Gary, that works well, but can I make it for a named range of cells?

The Cells I need to delete will be located around A10:A15 ( I have named
them address_block- and there will be blank cells above and below this range
which need to remain in place for formatting purposes.

C

 

[Gary''s Student]

Sub rowkiller()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Not Intersect(Cells(i, 1), Range("address_block")) Is Nothing Then
If IsEmpty(Cells(i, 1)) Then
Rows(i).Delete
End If
End If
Next
End Sub

This assumes that the Named Range has already been established on the
worksheet.

 

[Charlotte Howard]

Hi Gary,
Yes, the range was named, and this has worked a treat!
Thank you for your help,
Charlotte

Sub rowkiller()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Not Intersect(Cells(i, 1), Range("address_block")) Is Nothing Then
If IsEmpty(Cells(i, 1)) Then
Rows(i).Delete
End If
End If
Next
End Sub

This assumes that the Named Range has already been established on the
worksheet.

 

[JMB]

I think this would give the same results
range("address_block") .specialcells(xlcelltypeblanks).delete

Sub rowkiller()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Not Intersect(Cells(i, 1), Range("address_block")) Is Nothing Then
If IsEmpty(Cells(i, 1)) Then
Rows(i).Delete
End If
End If
Next
End Sub

This assumes that the Named Range has already been established on the
worksheet.

 

[Charlotte Howard]

Hi,
I think that I may need a bot more on this one. I had forgotten that there
will always be a parameter in Col A

A B
<paramenter> Address1
<paramenter> Address2
<paramenter>
<paramenter> Address3
<paramenter> Address4

any thoughts?

 

[JMB]

what do you want to do with the parameter? delete it as well? or is there
some condition that must be met before deleting the blank cells within your
named range "address block"?

you could delete the entire row with
range("address_block") .specialcells(xlcelltypeblanks).entirerow.delete

you could test for some condition in the column to the left of
"address_block", then delete the entire row

'------------------------------------------
Option Explicit

Sub test()
Dim rngCell As Range

For Each rngCell In Range("address_block").SpecialCells(xlCellTypeBlanks)
If rngCell.Offset(0, -1).Value = "some condition" Then
rngCell.EntireRow.Delete
End If
Next rngCell

End Sub

'------------------------------------------