delete rows

[helene and gabor]

Hello,

My simple program would make me believe that all rows 1 to 16 will be
deleted.
Not so. Only the odd numbered entries in col.A got deleted, entries
2,4,6,....16 remained.
My simple program is this:
------------------------------------------------------------------------------

Sub deleterows()
Dim i As Long
For i = 1 To 16
Rows(i).Delete
Next i
End Sub

--------------------------------------------------------------------------------
Thanks for your help.

Regards,

Gabor Sebo

 

[Wouter HM]

Hi Gabor,

When yor delete rows in this order the row counter gets mixed up.
As soon as the first row is deleted the second row becomes the 'new'
first row.
And so on.
So to remove rows using a for-next loop use:

Sub deleterows()
Dim i As Long
For i = 16 To 1 Step -1
Rows(i).Delete
Next i
End Sub


HTH,

Wouter

 

[Project Mangler]

Gabor,

To see why try:

Sub deleterows()
Dim i As Long
For i = 16 To 1 Step -1
Rows(i).Delete
Next i
End Sub

 

[Dave Peterson]

But if you wanted, you could take advantage of what excel did:

Sub deleterows()
Dim i As Long
For i = 1 To 16
Rows(1).Delete
Next i
End Sub

It's always deleting row 1 and shifting rows up.

You could use:
rows(1 & ":" & 16).delete
or
rows("1:16").delete
or
range("A1").resize(16,1).entirerow.delete

=========
But I expect that you're doing more than just deleting 16 rows. I bet you're
using some criteria to determine if you should delete the row.

You could use:

Dim myCell as range
dim myRng as range
dim delRng as range

with worksheets("Somesheetnamehere")
set myrng = .range("A1", .cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
if lcase(left(mycell.value,1)) = lcase("u") then
'keep it
else
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end if
next mycell

if delrng is nothing then
'nothing to delete
else
delrng.entirerow.delete
end if

 

[helene and gabor]

Hello all responders to my problem,

Your replies are logical and clear. I tried to respond to someone's problem:
Delete all rows below cell that has:" False" in it.
I now find the row number for the word :False, and delete lines from upper
limit to this row number step - 1.

Thanks very much!

Gabor Sebo