Range Delete not completely working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code. The purpose is to search the first column for a
predetermined value. If the value is found delete the first 7 columns of the
row and shift everything up.

The code works but does not completely delete everything. It will only
delete a few of the matches leaving the rest. The table in the sheet can
have from a few hundred entries to almost 20,000. Can anyone give me a fix
that will delete all the matches?

Thanks

' This code searchs the sheet for matching variables.
' If found it sets the range to delete and then deletes them shifting up
Do While xlSheet.Cells(Row, 1).Value <> ""
If xlSheet.Cells(Row, 1).Value = "2007-05" Then
Set BegRange = xlSheet.Cells(Row, 1)
Set EndRange = xlSheet.Cells(Row, 7)
xlSheet.Cells.Range(BegRange, EndRange).Delete (xlUp)
End If
Row = Row + 1
Loop
 
I have the following code. The purpose is to search the first column for a
predetermined value. If the value is found delete the first 7 columns of the
row and shift everything up.

The code works but does not completely delete everything. It will only
delete a few of the matches leaving the rest. The table in the sheet can
have from a few hundred entries to almost 20,000. Can anyone give me a fix
that will delete all the matches?

Thanks

' This code searchs the sheet for matching variables.
' If found it sets the range to delete and then deletes them shifting up
Do While xlSheet.Cells(Row, 1).Value <> ""
If xlSheet.Cells(Row, 1).Value = "2007-05" Then
Set BegRange = xlSheet.Cells(Row, 1)
Set EndRange = xlSheet.Cells(Row, 7)
xlSheet.Cells.Range(BegRange, EndRange).Delete (xlUp)
End If
Row = Row + 1
Loop
Click to expand...

You have posted this message to the wrong newsgroup.
The access in this groups name refers to Microsoft Access, a database
program.
Please repost to the correct newsgroup for the Excel program you are
using. I would suggest you include your Windows and Office version
number in the message.
 
fredg said:
You have posted this message to the wrong newsgroup.
The access in this groups name refers to Microsoft Access, a database
program.
Please repost to the correct newsgroup for the Excel program you are
using. I would suggest you include your Windows and Office version
number in the message.
Click to expand...

Thanks for the response Fred but I think is am in the correct news group.
The code I posted is from MS Access 2002. I am using MS Access to process
upwards of 400,000 records to build smaller recordsets. I then use VBA from
Access to populate an Excel workbook with the information I need to generate
a 94 page report containing about 600 charts.

Terry
 
Back
Top