Need Help with ActiveCell.EntireRow.Delete
 

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

Need Help with ActiveCell.EntireRow.Delete
 

You need to start from below, then work upwards:


Private Sub cmdCopy_Click()

Dim ClastRow As Integer, c As Range, l As Long

Application.ScreenUpdating = False

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 l = ClastRow To 2 Step -1

If Range("E" & l).Value < Worksheets("Sheet1").Range("I16").Value
Then
Rows(l).Delete
End If
Next c

Application.ScreenUpdating = True

End Sub



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ayo" wrote:

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

Need Help with ActiveCell.EntireRow.Delete
 

Someone suggested that yesteray and I tried it but I still ended up with the
same problem.

"Wigi" wrote:

You need to start from below, then work upwards:


Private Sub cmdCopy_Click()

Dim ClastRow As Integer, c As Range, l As Long

Application.ScreenUpdating = False

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 l = ClastRow To 2 Step -1

If Range("E" & l).Value < Worksheets("Sheet1").Range("I16").Value
Then
Rows(l).Delete
End If
Next c

Application.ScreenUpdating = True

End Sub



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ayo" wrote:

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

Need Help with ActiveCell.EntireRow.Delete
 

Thanks Wigi.
I was able to modify your code and now it looks like its working.

"Wigi" wrote:

You need to start from below, then work upwards:


Private Sub cmdCopy_Click()

Dim ClastRow As Integer, c As Range, l As Long

Application.ScreenUpdating = False

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 l = ClastRow To 2 Step -1

If Range("E" & l).Value < Worksheets("Sheet1").Range("I16").Value
Then
Rows(l).Delete
End If
Next c

Application.ScreenUpdating = True

End Sub



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ayo" wrote:

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

Need Help with ActiveCell.EntireRow.Delete
 

OK;, well done Ayo.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ayo" wrote:

Thanks Wigi.
I was able to modify your code and now it looks like its working.

"Wigi" wrote:

You need to start from below, then work upwards:


Private Sub cmdCopy_Click()

Dim ClastRow As Integer, c As Range, l As Long

Application.ScreenUpdating = False

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 l = ClastRow To 2 Step -1

If Range("E" & l).Value < Worksheets("Sheet1").Range("I16").Value
Then
Rows(l).Delete
End If
Next c

Application.ScreenUpdating = True

End Sub



--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Ayo" wrote:

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