"i:i" isn't a row it's a column.
--
Jim
|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
| > > | > > |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
| > >
| > >
| > >
