Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |