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

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

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