Loop/Deleting Rows 1

[Cartwheel]

How do continue an opertion until there is no more data? Or a specifeid number of times. I am deleting every other row, but I need it to continue for at leaast 100s of rows long:

' Keyboard Shortcut: Ctrl+w
'
Selection.EntireRow.Delete
Range("A2&quot.Select
Selection.EntireRow.Delete
Range("A3&quot.Select
Selection.EntireRow.Delete
Range("A4&quot.Select
Selection.EntireRow.Delete
Range("A5&quot.Select
Selection.EntireRow.Delete
Range("A6&quot.Select
Selection.EntireRow.Delete
Range("A7&quot.Select
Selection.EntireRow.Delete
Range("A8&quot.Select
Selection.EntireRow.Delete
Range("A9&quot.Select
Selection.EntireRow.Delete
Range("A10&quot.Select
Selection.EntireRow.Delete
Range("A11&quot.Select
Selection.EntireRow.Delete
Range("A12&quot.Select
Selection.EntireRow.Delete
Range("A13&quot.Select
Selection.EntireRow.Delete
Range("A14&quot.Select
Selection.EntireRow.Delete
Range("A15&quot.Select
Selection.EntireRow.Delete
Range("A16&quot.Select
Selection.EntireRow.Delete
Range("A17&quot.Select
Selection.EntireRow.Delete
Range("A18&quot.Select
Selection.EntireRow.Delete
Range("A19&quot.Select
Selection.EntireRow.Delete
Range("A20&quot.Select
Selection.EntireRow.Delete
Range("A21&quot.Select
Selection.EntireRow.Delete
Range("A22&quot.Select
Selection.EntireRow.Delete
Range("A23&quot.Select
Selection.EntireRow.Delete
Range("A24&quot.Select
Selection.EntireRow.Delete
Range("A25&quot.Select
Selection.EntireRow.Delete
Range("A26&quot.Select
Selection.EntireRow.Delete
Range("A27&quot.Select
Selection.EntireRow.Delete
Range("A28&quot.Select
End Sub

 

[Loomah]

Hi Cartwheel

ActiveSheet.UsedRange.Rows.EntireRow.Delete

will remove all data from your sheet. Completely!

;-) If a man says something and there are no women there to hear him, is he still wrong?
"...Three Lions On A Shirt..."

 

[xlbo]

Try this
For i = range("A65536&quot.end(xlup).row to 2 step -2
rows(i&":"&i).entirerow.delete
next i

This will delete every other row but be careful where you start it. If your last row doesn't need to be deleted but the one before it does, use .row-1 instead of .row

The other way to do it is to filter on blanks (or whatever cell value means that it should be deleted) and delete the entire lot in 1 go

HTH
Geoff

 

[Cartwheel]

Let me clarify, I'd like to continue deleting every other row down a column until there is no more data in that column...

So this list: Becomes:
A A
A B
B C
B D
C E
C F
D G
D
E
E
F
F
G
G

But the amount of data varies with each file, so I'd like to continue it until the last column with data is deleted... with up to a thousand rows of data...
Becomes this list


A
B
C
D
E
F
G

 

[Cartwheel]

Thanks a lot xlbo! That's exactly what I meant!

Are you sure:

rows(i&":"&i).entirerow.delete

is correct?

For some reason that part isn't working...

 

[xlbo]

You need to seperate the concatenators:
rows(i & ":" & i).entirerow.delete

Another thing springs to mind having seen a layout of your data. You appear to have duplicate values. If this is the case, then a nice simple way round it is to pivot table the data then copy the pivot table, paste as values, delete the value field of the PT and there ya go - unique list
HTh
Geoff

 

[Cartwheel]

Thank you! It works very well!