Need Help with ActiveCell.EntireRow.Delete

[Ayo]

I have this subroutine that is supose to look through the cells in a column
and if the cell value is equal to the value in a cell in another sheet, the
entire row is to be deleted. I have about 2807 rows in the sheet. The problem
I am having is that, this seem to work for about half of the rows. I should
have 332 rows left after this operation but I have 1403 rows because most of
the rows that should have been deleted are not.
Any ideas what is going on.

Private Sub cmdCopy_Click()
Dim ClastRow As Integer, c As Range

Worksheets("BulkUploadReport 1 ").Delete
Windows("BulkUploadReport 1 .xls").Activate
Sheets("BulkUploadReport 1 ").Select
Sheets("BulkUploadReport 1 ").Copy After:=Workbooks("Vista.xls").Sheets(1)

Sheets("BulkUploadReport 1 ").Select
ClastRow = ActiveSheet.UsedRange.Rows.Count
For Each c In ActiveSheet.Range("E2:E" & ClastRow).Cells
c.Activate
If c <> Worksheets("Sheet1").Range("I16").Value Then
ActiveCell.EntireRow.Delete
End If
Next c
End sub

 

[Jarek Kujawa]

try this:

Private Sub cmdCopy_Click()
Dim ClastRow As Integer, c As Range


Worksheets("BulkUploadReport 1 ").Delete
Windows("BulkUploadReport 1 .xls").Activate
Sheets("BulkUploadReport 1 ").Select
Sheets("BulkUploadReport 1 ").Copy
After:=Workbooks("Vista.xls").Sheets(1)


Sheets("BulkUploadReport 1 ").Select
ClastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
For Each c In ActiveSheet.Range("E2:E" & ClastRow).Cells
If c.Value <> Worksheets("Sheet1").Range("I16").Value Then
c.EntireRow.Delete
End If
Next c
End sub


HIH

 

[Jarek Kujawa]

sorry, this IS a better idea


Private Sub cmdCopy_Click()
Dim ClastRow As Integer, c As Range


Worksheets("BulkUploadReport 1 ").Delete
Windows("BulkUploadReport 1 .xls").Activate
Sheets("BulkUploadReport 1 ").Select
Sheets("BulkUploadReport 1 ").Copy
After:=Workbooks("Vista.xls").Sheets(1)


Sheets("BulkUploadReport 1 ").Select
ClastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
For i=ActiveSheet.Range("E2:E" & ClastRow).Cells.Count To 2 Step
-1
If cells(i,5).Value <> Worksheets("Sheet1").Range("I16").Value
Then
cells(i,5).EntireRow.Delete
End If
Next c
End sub

 

[Ayo]

This one gave me the same result as what I had before.

 

[Ayo]

This one didn't work at all.

 

[Gord Dibben]

Ayo

Are you sure the values are equal in the un-deleted cells?

Maybe an extra space?


Gord Dibben MS Excel MVP

 

[Ayo]

There are no extra space. I can't figure out what is going on. It work on
some of them but there are still a lot of cells it is not work on.

 

[Jarek Kujawa]

MY FAULT, sorry

Private Sub cmdCopy_Click()
Dim ClastRow As Integer, c As Range


Worksheets("BulkUploadReport 1 ").Delete
Windows("BulkUploadReport 1 .xls").Activate
Sheets("BulkUploadReport 1 ").Select
Sheets("BulkUploadReport 1 ").Copy
After:=Workbooks("Vista.xls").Sheets(1)


Sheets("BulkUploadReport 1 ").Select
ClastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
For i=ActiveSheet.Range("E2:E" & ClastRow).Cells.Count To 2 Step
-1
If cells(i,5).Value <> Worksheets("Sheet1").Range("I16").Value
Then
cells(i,5).EntireRow.Delete
End If
Next i
End sub


works fine on my Excel 2007

 

[Ayo]

Well I doesn't workfor me. Thanks any way. I will just keep trying. It's
really perplexing when you know your code is right but you are not getting
the expected result.