Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional deleting of rows
I am trying to delete rows based on 1. user selecting to overwrite data and
2. criteria met in both column A and column B. The code below is linked to a button on a form with 3 possible selections and the form pops up when data entered on another form is found to be duplicate to data already existing in the database. I use similar code to detect "duplicate rows" in the sub that brings up the selection form and it works. thanks in advance. Private Sub OKToProceed_Click() 'Check if user wants to overwrite duplicate data found then delete existing row 'with duplicate data and go back to mill measurements form If ExistingDataFound.Overwrite.Value = True Then 'SOMETHING WRONG...THE TEST FOR DUPLICATE ROWS DOESN'T WORK HERE Dim rngA As Range Dim dataws As Worksheet Set dataws = Worksheets("Data") With Worksheets("Data") Set rngA = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With 'CODE TO FIND DUPLICATE DATA For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st row If dataws.Cells(i, 1).Value = MeasDateMill1 And _ dataws.Cells(i, 2).Value = "Mill 1" Then Rows(i).Delete Unload ExistingDataFound Exit Sub End If Next i End If 'Check if user wants to keep existing value and add another row of data If ExistingDataFound.KeepExisting.Value = True Then Unload ExistingDataFound Exit Sub End If 'Check if user wants to cancel inputs and start over If ExistingDataFound.CancelandRevise.Value = True Then MillMeasurements.MeasDateMill1.Value = "" MillMeasurements.MeasDateMill2.Value = "" MillMeasurements.MeasDateCoalMill.Value = "" Unload ExistingDataFound Exit Sub End If Dim noselect As Long noselect = MsgBox("No Selection Made", vbOKOnly) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional deleting of rows
It is helpful to know the Excel version and Windows operating system when trying
to remedy a problem. XL4, XL5 and XL12 operate to their own drummer. A Mac OS does not have all components that some code requires. It also helps to know which line of code creates the error and what error is generated. However, this line... Set rngA = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) Should read... Set rngA = .Cells(.Rows.Count, "A").End(xlUp) -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Brian" wrote in message I am trying to delete rows based on 1. user selecting to overwrite data and 2. criteria met in both column A and column B. The code below is linked to a button on a form with 3 possible selections and the form pops up when data entered on another form is found to be duplicate to data already existing in the database. I use similar code to detect "duplicate rows" in the sub that brings up the selection form and it works. thanks in advance. Private Sub OKToProceed_Click() 'Check if user wants to overwrite duplicate data found then delete existing row 'with duplicate data and go back to mill measurements form If ExistingDataFound.Overwrite.Value = True Then 'SOMETHING WRONG...THE TEST FOR DUPLICATE ROWS DOESN'T WORK HERE Dim rngA As Range Dim dataws As Worksheet Set dataws = Worksheets("Data") With Worksheets("Data") Set rngA = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With 'CODE TO FIND DUPLICATE DATA For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st row If dataws.Cells(i, 1).Value = MeasDateMill1 And _ dataws.Cells(i, 2).Value = "Mill 1" Then Rows(i).Delete Unload ExistingDataFound Exit Sub End If Next i End If 'Check if user wants to keep existing value and add another row of data If ExistingDataFound.KeepExisting.Value = True Then Unload ExistingDataFound Exit Sub End If 'Check if user wants to cancel inputs and start over If ExistingDataFound.CancelandRevise.Value = True Then MillMeasurements.MeasDateMill1.Value = "" MillMeasurements.MeasDateMill2.Value = "" MillMeasurements.MeasDateCoalMill.Value = "" Unload ExistingDataFound Exit Sub End If Dim noselect As Long noselect = MsgBox("No Selection Made", vbOKOnly) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional deleting of rows
I'm working in EXCEL 2003 (not sure what XL4, 5 and 12 are) on Windows XP.
Also, I made the change you noted below but no improvement. "Jim Cone" wrote: It is helpful to know the Excel version and Windows operating system when trying to remedy a problem. XL4, XL5 and XL12 operate to their own drummer. A Mac OS does not have all components that some code requires. It also helps to know which line of code creates the error and what error is generated. However, this line... Set rngA = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) Should read... Set rngA = .Cells(.Rows.Count, "A").End(xlUp) -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Brian" wrote in message I am trying to delete rows based on 1. user selecting to overwrite data and 2. criteria met in both column A and column B. The code below is linked to a button on a form with 3 possible selections and the form pops up when data entered on another form is found to be duplicate to data already existing in the database. I use similar code to detect "duplicate rows" in the sub that brings up the selection form and it works. thanks in advance. Private Sub OKToProceed_Click() 'Check if user wants to overwrite duplicate data found then delete existing row 'with duplicate data and go back to mill measurements form If ExistingDataFound.Overwrite.Value = True Then 'SOMETHING WRONG...THE TEST FOR DUPLICATE ROWS DOESN'T WORK HERE Dim rngA As Range Dim dataws As Worksheet Set dataws = Worksheets("Data") With Worksheets("Data") Set rngA = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With 'CODE TO FIND DUPLICATE DATA For i = rngA.Count To 1 Step -1 ' Test from bottom of range to 1st row If dataws.Cells(i, 1).Value = MeasDateMill1 And _ dataws.Cells(i, 2).Value = "Mill 1" Then Rows(i).Delete Unload ExistingDataFound Exit Sub End If Next i End If 'Check if user wants to keep existing value and add another row of data If ExistingDataFound.KeepExisting.Value = True Then Unload ExistingDataFound Exit Sub End If 'Check if user wants to cancel inputs and start over If ExistingDataFound.CancelandRevise.Value = True Then MillMeasurements.MeasDateMill1.Value = "" MillMeasurements.MeasDateMill2.Value = "" MillMeasurements.MeasDateCoalMill.Value = "" Unload ExistingDataFound Exit Sub End If Dim noselect As Long noselect = MsgBox("No Selection Made", vbOKOnly) End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
deleting rows conditional... help!!! | Excel Discussion (Misc queries) | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |