Delete Row If OracleID = Value
 

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

Delete Row If OracleID = Value
 

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)

"Connie" wrote in message
ups.com...
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

Delete Row If OracleID = Value
 

"Bob Phillips" wrote in message
...
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

Delete Row If OracleID = Value
 

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" wrote in message
...
"Bob Phillips" wrote in message
...
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

Delete Row If OracleID = Value
 

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

keizi

"Bob Phillips" wrote in message
...
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" wrote in message
...
"Bob Phillips" wrote in message
...
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

Delete Row If OracleID = Value
 

Sorry, my if clause never happen in your code.

"kounoike" wrote in message
...
Well, I understand, if the lastrow is in autofilter range.

keizi

"Bob Phillips" wrote in message
...
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

Delete Row If OracleID = Value
 

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.




kounoike wrote:
Sorry, my if clause never happen in your code.

"kounoike" wrote in message
...
Well, I understand, if the lastrow is in autofilter range.

keizi

"Bob Phillips" wrote in message
...
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

Delete Row If OracleID = Value
 

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" wrote in message
oups.com...
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.




kounoike wrote:
Sorry, my if clause never happen in your code.

"kounoike" wrote in message
...
Well, I understand, if the lastrow is in autofilter range.

keizi

"Bob Phillips" wrote in message
...
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

Delete Row If OracleID = Value
 

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" wrote in message
oups.com...
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.




kounoike wrote:
Sorry, my if clause never happen in your code.

"kounoike" wrote in message
...
Well, I understand, if the lastrow is in autofilter range.

keizi

"Bob Phillips" wrote in message
...
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

Delete Row If OracleID = Value
 

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" wrote in message
...
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" wrote in message
oups.com...
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.




kounoike wrote:
Sorry, my if clause never happen in your code.

"kounoike" wrote in message
...
Well, I understand, if the lastrow is in autofilter range.

keizi

"Bob Phillips" wrote in message
...
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