Delete Row If OracleID = Value

Connie · Oct 11, 2006

I have the following data:

EndDate EmployeeName OracleID TechNo
9/30/2006 Tim Jones 12345 1234
10/1/2006 Tim Jones 12345 1234
10/2/2006 Tim Jones 12345 1234
10/3/2006 Tim Jones 12345 1234
10/4/2006 Tim Jones 12345 1234
10/5/2006 Tim Jones 12345 1234
10/6/2006 Tim Jones 12345 1234
9/30/2006 Jan Clark 34567 0
10/1/2006 Jan Clark 34567 0
10/2/2006 Jan Clark 34567 0
10/3/2006 Jan Clark 34567 0
10/4/2006 Jan Clark 34567 0
10/5/2006 Jan Clark 34567 0
10/6/2006 Jan Clark 34567 0
9/30/2006 Joe Hall 34566 2345
10/1/2006 Joe Hall 34566 2345
10/2/2006 Joe Hall 34566 2345
10/3/2006 Joe Hall 34566 2345
10/4/2006 Joe Hall 34566 2345
10/5/2006 Joe Hall 34566 2345
10/6/2006 Joe Hall 34566 2345

I am trying to delete all rows in the range if the OracleID is equal to
a particular value. My first attemt was to do a VBLOOKUP to find rows
that matched the Oracle number, but I'm wondering if there's an easier
way to do it. I don't think the VBLOOKUP will work because I will have
multiple records with the same OracleID. I guess I could loop through
the data (ugh!) and delete a row if the OracleID matches. If I do
that, what's the syntax to say "delete this row"?

Here's the code I'm using to perform the VBLOOKUP:

Private Sub Check_For_Existing_Oracle_No_Click()
Dim rng As Range
Dim Test As Variant
Sheets("Upload Data").Select
Set rng = GetRealLastCell(ActiveSheet)
lookuprange = ("$C$2:" + rng.Address)
Test = Application.VLookup(Me.Range("oracle_no").Value, _
Sheets("Upload Data").Range(lookuprange), 1, False)
If IsError(Test) Then
MsgBox "It wasn't found"
Else
Delete row -- not sure how to do this
End If
End Sub

Bob Phillips · Oct 11, 2006

Private Sub Check_For_Existing_Oracle_No_Click()
Dim iLastRow As Long
Dim rng As Range
Worksheets("Upload Data").Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("C1:C" & iLastRow)
rng.AutoFilter
rng.AutoFilter field:=1, Criteria1:=Range("oracle_no").Value
Set rng = Range("C2:C2" & iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
Range("C1").AutoFilter
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

kounoike · Oct 11, 2006

Bob Phillips said:
Private Sub Check_For_Existing_Oracle_No_Click()
Dim iLastRow As Long
Dim rng As Range
Worksheets("Upload Data").Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("C1:C" & iLastRow)
rng.AutoFilter
rng.AutoFilter field:=1, Criteria1:=Range("oracle_no").Value
Set rng = Range("C2:C2" & iLastRow).SpecialCells(xlCellTypeVisible)

Is the code above typo? Maybe

Set rng = Range("C2:C" & iLastRow).SpecialCells(xlCellTypeVisible)

keizi

Bob Phillips · Oct 11, 2006

It is. Couldn't have had a problem in the tests as it just extended the
range to 2 & iLastRow, so if iLastrow was 72, it would set it to 272, not a
problem, just a waste.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

kounoike · Oct 11, 2006

Well, I understand, if the lastrow is in autofilter range.

keizi

kounoike · Oct 11, 2006

Sorry, my if clause never happen in your code.

kounoike said:
Well, I understand, if the lastrow is in autofilter range.

keizi

Connie · Oct 12, 2006

Thanks for your help. I'm still not able to get this to work. The
ilastrow returns a value which is not the last row in the range. Also,
a delete takes place, but it takes place from the sheet from which the
command button was called, and not the "Upload Data" sheet.

Help!

Here's the data I'm using. Should I not expect ilastrow to equal 29?
Also, what does Cells(Rows.Count, "A") mean? Thanks again.

Bob Phillips · Oct 12, 2006

How would we know what to expect iLastRow to be, we cannot see the data.

Cells(Rows.Count, "A") just finds the last row in the worksheet, then
..End(xlUp) moves up to the previous (i.e. very last) data row, .Row gets
that row number.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

kounoike · Oct 12, 2006

you seem to copy Bob's code to Worksheet module. if that is the case, i
think Bob's code would not work as it is, but needs some modifications. i
think a simple way is to copy Bob's code to a standard module and remove
"Private" from Sub statement and call it from your button like below. but
i'm not sure this willl work and way to go.

Private Sub CommandButton1_Click() '<<==Change to your button name
Check_For_Existing_Oracle_No_Click
End Sub

Sub Check_For_Existing_Oracle_No_Click()
Dim iLastRow As Long
Dim rng As Range
Worksheets("Upload Data").Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("C1:C" & iLastRow)
rng.AutoFilter
rng.AutoFilter field:=1, Criteria1:=Range("oracle_no").Value
Set rng = Range("C2:C" & iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
Range("C1").AutoFilter
End Sub

keizi

Bob Phillips · Oct 12, 2006

Connie started with a Private button event code, I just adapted it, so I
think that would not be the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

kounoike said:
you seem to copy Bob's code to Worksheet module. if that is the case, i
think Bob's code would not work as it is, but needs some modifications. i
think a simple way is to copy Bob's code to a standard module and remove
"Private" from Sub statement and call it from your button like below. but
i'm not sure this willl work and way to go.

Private Sub CommandButton1_Click() '<<==Change to your button name
Check_For_Existing_Oracle_No_Click
End Sub

Sub Check_For_Existing_Oracle_No_Click()
Dim iLastRow As Long
Dim rng As Range
Worksheets("Upload Data").Select
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("C1:C" & iLastRow)
rng.AutoFilter
rng.AutoFilter field:=1, Criteria1:=Range("oracle_no").Value
Set rng = Range("C2:C" & iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
Range("C1").AutoFilter
End Sub

keizi

Connie · Oct 12, 2006

My apologies. My intent was to copy the data in the post, but
apparently I didn't. My bad.

I made a couple of changes to the code to fix the issue that the rows
were being deleted from the sheet in which the command button was
called rather than the sheet "Upload Data". When I check for ilastrow,
the value is 15. When I run the code, it deletes rows C2:C15 on the
"Upload Data" sheet. The oracle_no is 12345. I ran the code and
included a message box to check the oracle_no, and it returns blank.
I've included the data below BEFORE I run the code and also AFTER I run
the code. What I want is for rows C2:C8 to be deleted, since those are
the rows for which have oracle_no=12345. Thanks for your help!

Private Sub Check_For_Existing_Oracle_No_Click()
Dim iLastRow As Long
Dim rng As Range
Dim sh As Worksheet
Set sh = Worksheets("Compiled Totals")
iLastRow = Cells(sh.Rows.Count, "A").End(xlUp).Row
Set rng = sh.Range("C1:C" & iLastRow)
'Check oracle_no to make sure it was passed
MsgBox oracle_no
rng.AutoFilter
rng.AutoFilter field:=1, Criteria1:=Range("oracle_no").Value
Set rng = Sheets("Upload Data").Range("C2:C" &
iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
Sheets("Upload Data").Range("C1").AutoFilter
End Sub
***************************************************************************************************
BEFORE Data:

EndDate EmployeeName OracleID TechNo
9/30/2006 Test 12345 0
10/1/2006 Test 12345 0
10/2/2006 Test 12345 0
10/3/2006 Test 12345 0
10/4/2006 Test 12345 0
10/5/2006 Test 12345 0
10/6/2006 Test 12345 0
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
**************************************************************************************
AFTER data:

EndDate EmployeeName OracleID TechNo
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345

Bob said:
How would we know what to expect iLastRow to be, we cannot see the data.

Cells(Rows.Count, "A") just finds the last row in the worksheet, then
.End(xlUp) moves up to the previous (i.e. very last) data row, .Row gets
that row number.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Connie · Oct 12, 2006

My apologies in the last post. My intent was to copy the data in the
post, but
apparently I didn't. My bad.

I made a couple of changes to the code to fix the issue that the rows
were being deleted from the sheet in which the command button was
called rather than the sheet "Upload Data". When I check for ilastrow,

the value is 15. When I run the code, it deletes rows C2:C15 on the
"Upload Data" sheet. The oracle_no is 12345. I ran the code and
included a message box to check the oracle_no, and it returns blank.
I've included the data below BEFORE I run the code and also AFTER I run

the code. What I want is for rows C2:C8 to be deleted, since those are

the rows for which have oracle_no=12345. Thanks for your help!

Private Sub Check_For_Existing_Oracle_No_Click()
Dim iLastRow As Long
Dim rng As Range
Dim sh As Worksheet
Set sh = Worksheets("Compiled Totals")
iLastRow = Cells(sh.Rows.Count, "A").End(xlUp).Row
Set rng = sh.Range("C1:C" & iLastRow)
'Check oracle_no to make sure it was passed
MsgBox oracle_no
rng.AutoFilter
rng.AutoFilter field:=1, Criteria1:=Range("oracle_no").Value
Set rng = Sheets("Upload Data").Range("C2:C" &
iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
Sheets("Upload Data").Range("C1").AutoFilter
End Sub
***************************************************************************************************

BEFORE Data:

EndDate EmployeeName OracleID TechNo
9/30/2006 Test 12345 0
10/1/2006 Test 12345 0
10/2/2006 Test 12345 0
10/3/2006 Test 12345 0
10/4/2006 Test 12345 0
10/5/2006 Test 12345 0
10/6/2006 Test 12345 0
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
**************************************************************************************

AFTER data:

EndDate EmployeeName OracleID TechNo
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345

Bob said:
Connie started with a Private button event code, I just adapted it, so I
think that would not be the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

kounoike · Oct 13, 2006

i think your code just to delete rows from 2 to 15, whatever your data is.
if you don't want to change the place your code is in, try this one.

Private Sub Check_For_Existing_Oracle_No_Click1()
Dim iLastRow As Long
Dim rng As Range
With Worksheets("Upload Data")
iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng = .Range("C1:C" & iLastRow)
rng.AutoFilter
rng.AutoFilter field:=1, Criteria1:=Range("oracle_no").Value
Set rng = .Range("C2:C" & iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
.Range("C1").AutoFilter
End With
End Sub

keizi

My apologies in the last post. My intent was to copy the data in the
post, but
apparently I didn't. My bad.

I made a couple of changes to the code to fix the issue that the rows
were being deleted from the sheet in which the command button was
called rather than the sheet "Upload Data". When I check for ilastrow,

the value is 15. When I run the code, it deletes rows C2:C15 on the
"Upload Data" sheet. The oracle_no is 12345. I ran the code and
included a message box to check the oracle_no, and it returns blank.
I've included the data below BEFORE I run the code and also AFTER I run

the code. What I want is for rows C2:C8 to be deleted, since those are

the rows for which have oracle_no=12345. Thanks for your help!

Private Sub Check_For_Existing_Oracle_No_Click()
Dim iLastRow As Long
Dim rng As Range
Dim sh As Worksheet
Set sh = Worksheets("Compiled Totals")
iLastRow = Cells(sh.Rows.Count, "A").End(xlUp).Row
Set rng = sh.Range("C1:C" & iLastRow)
'Check oracle_no to make sure it was passed
MsgBox oracle_no
rng.AutoFilter
rng.AutoFilter field:=1, Criteria1:=Range("oracle_no").Value
Set rng = Sheets("Upload Data").Range("C2:C" &
iLastRow).SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
Sheets("Upload Data").Range("C1").AutoFilter
End Sub
***************************************************************************************************

BEFORE Data:

EndDate EmployeeName OracleID TechNo
9/30/2006 Test 12345 0
10/1/2006 Test 12345 0
10/2/2006 Test 12345 0
10/3/2006 Test 12345 0
10/4/2006 Test 12345 0
10/5/2006 Test 12345 0
10/6/2006 Test 12345 0
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
**************************************************************************************

AFTER data:

EndDate EmployeeName OracleID TechNo
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345
10/14/2006 John Smith 23456 2345
10/15/2006 John Smith 23456 2345
10/16/2006 John Smith 23456 2345
10/17/2006 John Smith 23456 2345
10/18/2006 John Smith 23456 2345
10/19/2006 John Smith 23456 2345
10/20/2006 John Smith 23456 2345

Bob said:
Connie started with a Private button event code, I just adapted it, so I
think that would not be the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

kounoike · Oct 13, 2006

Sorry, I misspelled sub name, Change

Private Sub Check_For_Existing_Oracle_No_Click1()
to
Private Sub Check_For_Existing_Oracle_No_Click()

keizi

Sorting Problem	1	Oct 11, 2006
Detect non-blank range	2	Oct 16, 2006
Macro to delete row if cells greater than certain value	1	Dec 3, 2012
Delete Row Help	2	Jul 11, 2007
Delete Rows and Parse Data	0	Jun 26, 2004
Multiple If statements, Calculating quarterly % holding costs	0	Nov 23, 2012
Strange vlookup results	17	May 26, 2006
Referencing a cell where lookup value is not on same row	6	Nov 20, 2008

Delete Row If OracleID = Value

Connie

Bob Phillips

kounoike

Bob Phillips

kounoike

kounoike

Connie

Bob Phillips

kounoike

Bob Phillips

Connie

Connie

kounoike

kounoike

Ask a Question

Similar Threads