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
|