rows that dont want to be deleted!!!!

  • Thread starter Thread starter cecilia12345
  • Start date Start date
C

cecilia12345

i have this code:
lets say numfilas is 6
i want to delete the first *6* rows

Sheets(2).Select
For i = 1 To NumFilas
Range("a1").EntireRow.Delete
Next i

it deletes some rows but some are just not deleted... why?

i have tried also
cells(i,1).entirerow.delete
and
row(i).delete
 
Cecilia,

The reason is that when you delete row 1, what used to be row 2
now becomes row 1. Then the loop variable is incremented from 1
to 2, and the new row 2 (which used to be row 3) is deleted,
leaving the original row 2 (now row 1) in place. This behavior
repeats itself with each iteration of the loop.

When deleting rows in a loop, you should go in descending order:

Dim Ndx As Long
For Ndx = 6 To 1 Step -1
Rows(Ndx).Delete
Next Ndx

You could do this without a loop, of course.

Range("A1:A6").EntireRow.Delete


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Range("A1:A6").EntireRow.Delete

Would that work as:

Range("A1:A" & Ndx).EntireRow.Delete ??

Ed

Chip Pearson said:
Cecilia,

The reason is that when you delete row 1, what used to be row 2
now becomes row 1. Then the loop variable is incremented from 1
to 2, and the new row 2 (which used to be row 3) is deleted,
leaving the original row 2 (now row 1) in place. This behavior
repeats itself with each iteration of the loop.

When deleting rows in a loop, you should go in descending order:

Dim Ndx As Long
For Ndx = 6 To 1 Step -1
Rows(Ndx).Delete
Next Ndx

You could do this without a loop, of course.

Range("A1:A6").EntireRow.Delete


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
Back
Top