| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need Help with ActiveCell.EntireRow.Delete | Excel Discussion (Misc queries) | |||
| ListBox1. add item myCell.entirerow | Excel Discussion (Misc queries) | |||
| If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
| ActiveCell or ActiveSheet | Excel Worksheet Functions | |||
| delete entirerow if date more than 12months old | Excel Discussion (Misc queries) | |||
Linear Mode
