| 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
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help with ActiveCell.EntireRow.Delete
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help with ActiveCell.EntireRow.Delete
This one gave me the same result as what I had before.
"Jarek Kujawa" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help with ActiveCell.EntireRow.Delete
This one didn't work at all.
"Jarek Kujawa" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help with ActiveCell.EntireRow.Delete
Ayo
Are you sure the values are equal in the un-deleted cells? Maybe an extra space? Gord Dibben MS Excel MVP On Fri, 18 Jul 2008 20:05:00 -0700, Ayo wrote: This one gave me the same result as what I had before. "Jarek Kujawa" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help with ActiveCell.EntireRow.Delete
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. "Gord Dibben" wrote: Ayo Are you sure the values are equal in the un-deleted cells? Maybe an extra space? Gord Dibben MS Excel MVP On Fri, 18 Jul 2008 20:05:00 -0700, Ayo wrote: This one gave me the same result as what I had before. "Jarek Kujawa" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help with ActiveCell.EntireRow.Delete
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need Help with ActiveCell.EntireRow.Delete
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. "Jarek Kujawa" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 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) | |||
| highlight activecell | Excel Discussion (Misc queries) | |||
Linear Mode
