Auto delete rows within a specified range
 

I'm new to VBA and trying to learn how to write code. My objective is
to automatically delete entire rows between row 19 and row 28 when the
value in column 11 in any of these rows is empty (null). Following is
the code I've written which, of course, does not work.

Public Sub Delete_Rows()

Dim GlobalRange As Range

Dim Range As Range

Set GlobalRange = Application.ActiveSheet.Range("N19:N28")

For Each Range In GlobalRange
If Range.Cells = "null" Then EntireRow.Delete

Next


End Sub

Auto delete rows within a specified range
 

Hi,

Have a look at:
http://excelforum.com/showthread.php...light=broro183
This include's a link to a range of methods that Ron Debruin provide
on his page:
http://www.rondebruin.nl/delete.htm

re your issue, I don't have the solution but here's a couple o
suggestions:
1) I'd change your second range from "Dim Range As Range" to somethin
unique eg "SingleCellRange" as this may be confusing Excel.
2) I think "null" only relates to VBA variables not cell values (may b
wrong) & it shouldn't be ".cells", so it may be best to change thi
from:

If Range.Cells = "" then
to
If len(SingleCellRange.value)=0 then
or
If SingleCellRange.value = "" then

hth,
Rob Brockett
Always learning & the best way to learn is to experience..

--
broro18
-----------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...fo&userid=3006
View this thread: http://www.excelforum.com/showthread.php?threadid=55185

Auto delete rows within a specified range
 

You need to delete going backwords - otherwise, the when you delete, rows
shift and you can end up skipping rows. ex: If I delete row 19, row 20
becomes 19, so the next row I check is the old row 21 and row 20 doesn't get
checked.

Public Sub Delete_Rows()
Dim i as Long
for i = 28 to 19 step -1
if isempty(cells(i,"N")) then
rows(i).Delete
End if
Next
End Sub

--
Regards,
Tom Ogilvy


" wrote:

I'm new to VBA and trying to learn how to write code. My objective is
to automatically delete entire rows between row 19 and row 28 when the
value in column 11 in any of these rows is empty (null). Following is
the code I've written which, of course, does not work.

Public Sub Delete_Rows()

Dim GlobalRange As Range

Dim Range As Range

Set GlobalRange = Application.ActiveSheet.Range("N19:N28")

For Each Range In GlobalRange
If Range.Cells = "null" Then EntireRow.Delete

Next


End Sub