unable to delete rows in excel....?? 1

[nimarii]

Hello -

I have an access module that needs to edit an excel spreadsheet. I'm trying to delete the first 7 rows of the spreadsheet regardless of whatever data or blanks is in it, but for some reason, it seems to be deleting only the odd rows. (maybe i'm just imagining things?)
here's the code:

Private Sub Import_Click()
Dim xlapp As Excel.Application
Dim xlwb As Excel.Workbook
Dim sheet As Excel.Worksheet
Set xlapp = GetObject(, "Excel.Application")
Me.PathName.SetFocus
Path = Me.PathName.Text
Set xlwb = xlapp.Workbooks.Open(Path)
Set sheet = xlapp.ActiveWorkbook.Sheets(1)

With xlapp
  For r = 1 To 7 Step 1
      sheet.Rows(r).Delete
  Next r
End With

'some more code...

can anyone see what i'm doing wrong? i'd greatly appreciate any help!

 

[Molby]

The reason is because when you delete a row it moves the data up, use this instead

For r = 1 To 7 Step 1
      Rows(r / r).Delete
Next r

 

[nimarii]

ahhhh - i see. clever idea, thanks!

 

[KenWright]

The usual way of doing this is to step backwards through the rows, eg:-

For r = 7 To 1 Step -1
sheet.Rows(r).Delete
Next r

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[FacilitiesCAD]

I guess I would just:
For r = 1 To 7 Step 1
sheet.Rows(1).Delete
Next r

but we all get the same job done.

Tim