Delete blank rows
 

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

Delete blank rows
 

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

Delete blank rows
 

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

Delete blank rows
 

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

Delete blank rows
 

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

Delete blank rows
 

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

Delete blank rows
 

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

Delete blank rows
 

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(xlCellTypeBlan ks)
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