Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
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 "helene and gabor" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ "helene and gabor" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofil on variable rows, delete extract and show remaining rows | Excel Programming | |||
Delete & Merge Columns,Delete Rows with filter, etc | Excel Programming | |||
Delete Rows if any cell in Column H is blank but do not Delete Fir | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |