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

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

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


"Charlotte Howard" wrote:

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