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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
>

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

keizi

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>>
>
>

Sorry, my if clause never happen in your code.

"kounoike" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Well, I understand, if the lastrow is in autofilter range.
>
> keizi
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>

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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Well, I understand, if the lastrow is in autofilter range.
> >
> > keizi
> >
> > "Bob Phillips" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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
> >>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > Well, I understand, if the lastrow is in autofilter range.
> > >
> > > keizi
> > >
> > > "Bob Phillips" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > >> 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
> > >>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > Well, I understand, if the lastrow is in autofilter range.
>> >
>> > keizi
>> >
>> > "Bob Phillips" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> 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
>> >>
>

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" <(E-Mail Removed)> wrote in message
news:OQjN%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >> > Well, I understand, if the lastrow is in autofilter range.
> >> >
> >> > keizi
> >> >
> >> > "Bob Phillips" <(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> >> 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
> >> >>
> >
>