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

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
--
Gary''s Student - gsnu200807


"Charlotte Howard" wrote:

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

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" wrote:

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
--
Gary''s Student - gsnu200807

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.
--
Gary''s Student - gsnu200807


"Charlotte Howard" wrote:

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" wrote:

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
--
Gary''s Student - gsnu200807

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200807


"Charlotte Howard" wrote:

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" wrote:

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
--
Gary''s Student - gsnu200807

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200807


"Charlotte Howard" wrote:

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" wrote:

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
--
Gary''s Student - gsnu200807

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" wrote:

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200807


"Charlotte Howard" wrote:

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" wrote:

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
--
Gary''s Student - gsnu200807