Deleting rows
 

Thanks, Jim, but still no luck. What is most strange is I have the code
below in another sub that works fine (this one loads the selection form which
includes the code that is not working tied to a specific selection made).
I've tested to see if I'm passing the test for the first If/Then statement
and that is working but something between the For/Next statements are failing.

==================
Private Sub MeasDateMill1_Change()
'Check to see if data with same date/mill have already been entered in the
database
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
Dim ans As Long
ans = MsgBox("Measurements for this date on this mill already exist
in the Database.", vbOKOnly)
' ExistingDataFound.Show Eliminating this pop up until can figure
out how to solve problem of overwriting data
' For the time being will give a pop up only telling the user there
is data with this date in the db already
'
Mill1DuplicateFound.Show
Exit Sub
'
End If
Next i
End Sub
"Jim Rech" wrote:

Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp)

This sets a one cell range equal to the last cell in A with an entry.

You have to specifiy with the begins and ends:

Set rngA = Range(.Cells(1,1), .Cells(.Rows.Count, "A").End(xlUp))

--
Jim
"Brian" wrote in message
...
|I am trying to delete rows if column A has value X and column B has value
Y.
| My If test does not seem to be working in this particular sub (although I
| have virtually identical code in another sub that does work). I'm at a
loss.
| Code is below...
|
| If ExistingDataFound.Overwrite.Value = True Then
| Dim rngA As Range
| Dim dataws As Worksheet
| Set dataws = Worksheets("Data")
| With Worksheets("Data")
| Set rngA = dataws.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 Shift:=xlUp
| Unload ExistingDataFound
| Exit Sub
| End If
| Next i
| End If

Deleting rows
 

I figured out my problem. The test for "MeasDateMill1" is from another form
and for a reason I don't know is causing the problem. I can work around
this, though. Next problem is I'm getting an application or object not
defined errow on the "rows(i).Delete line. I've also tried without success:

Rows ("i:i").Select
Selection.Delete Shift:xlUp

"Brian" wrote:

Thanks, Jim, but still no luck. What is most strange is I have the code
below in another sub that works fine (this one loads the selection form which
includes the code that is not working tied to a specific selection made).
I've tested to see if I'm passing the test for the first If/Then statement
and that is working but something between the For/Next statements are failing.

==================
Private Sub MeasDateMill1_Change()
'Check to see if data with same date/mill have already been entered in the
database
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
Dim ans As Long
ans = MsgBox("Measurements for this date on this mill already exist
in the Database.", vbOKOnly)
' ExistingDataFound.Show Eliminating this pop up until can figure
out how to solve problem of overwriting data
' For the time being will give a pop up only telling the user there
is data with this date in the db already
'
Mill1DuplicateFound.Show
Exit Sub
'
End If
Next i
End Sub
"Jim Rech" wrote:

Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp)

This sets a one cell range equal to the last cell in A with an entry.

You have to specifiy with the begins and ends:

Set rngA = Range(.Cells(1,1), .Cells(.Rows.Count, "A").End(xlUp))

--
Jim
"Brian" wrote in message
...
|I am trying to delete rows if column A has value X and column B has value
Y.
| My If test does not seem to be working in this particular sub (although I
| have virtually identical code in another sub that does work). I'm at a
loss.
| Code is below...
|
| If ExistingDataFound.Overwrite.Value = True Then
| Dim rngA As Range
| Dim dataws As Worksheet
| Set dataws = Worksheets("Data")
| With Worksheets("Data")
| Set rngA = dataws.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 Shift:=xlUp
| Unload ExistingDataFound
| Exit Sub
| End If
| Next i
| End If

Deleting rows
 

"i:i" isn't a row it's a column.

--
Jim
"Brian" wrote in message
...
|I figured out my problem. The test for "MeasDateMill1" is from another
form
| and for a reason I don't know is causing the problem. I can work around
| this, though. Next problem is I'm getting an application or object not
| defined errow on the "rows(i).Delete line. I've also tried without
success:
|
| Rows ("i:i").Select
| Selection.Delete Shift:xlUp
|
| "Brian" wrote:
|
| Thanks, Jim, but still no luck. What is most strange is I have the code
| below in another sub that works fine (this one loads the selection form
which
| includes the code that is not working tied to a specific selection
made).
| I've tested to see if I'm passing the test for the first If/Then
statement
| and that is working but something between the For/Next statements are
failing.
|
| ==================
| Private Sub MeasDateMill1_Change()
| 'Check to see if data with same date/mill have already been entered in
the
| database
| 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
| Dim ans As Long
| ans = MsgBox("Measurements for this date on this mill already
exist
| in the Database.", vbOKOnly)
| ' ExistingDataFound.Show Eliminating this pop up until can
figure
| out how to solve problem of overwriting data
| ' For the time being will give a pop up only telling the user
there
| is data with this date in the db already
| '
| Mill1DuplicateFound.Show
| Exit Sub
| '
| End If
| Next i
| End Sub
| "Jim Rech" wrote:
|
| Set rngA = dataws.Cells(.Rows.Count, "A").End(xlUp)
|
| This sets a one cell range equal to the last cell in A with an entry.
|
| You have to specifiy with the begins and ends:
|
| Set rngA = Range(.Cells(1,1), .Cells(.Rows.Count, "A").End(xlUp))
|
| --
| Jim
| "Brian" wrote in message
| ...
| |I am trying to delete rows if column A has value X and column B has
value
| Y.
| | My If test does not seem to be working in this particular sub
(although I
| | have virtually identical code in another sub that does work). I'm
at a
| loss.
| | Code is below...
| |
| | If ExistingDataFound.Overwrite.Value = True Then
| | Dim rngA As Range
| | Dim dataws As Worksheet
| | Set dataws = Worksheets("Data")
| | With Worksheets("Data")
| | Set rngA = dataws.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 Shift:=xlUp
| | Unload ExistingDataFound
| | Exit Sub
| | End If
| | Next i
| | End If
|
|
|