Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying Code that deletes a Row
Hey guys
Happy Thanksgiving. Here is a code(see below) that deletes the entire row if Textbox1,2,and3 match data found in a row in range A1:A3 starting in A1. What would the code be, that would put the number of the row to be deleted in cell K1, instead of actually deleting the entire Row that matches the criteria in the 3 textboxes? I dont want to delete the row, just put the row to be deleted in cell K1. Private Sub CommandButton6_Click() If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub End If Dim c Dim firstAddress As String, tmp As Integer Dim tmp_array() With Worksheets(2) Set c = .Range("A1").CurrentRegion.Columns(1).Find (TextBox1.Text, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do If c.Offset(0, 1).Value = TextBox2.Text And c.Offset(0, 2).Value = TextBox3.Text Then On Error GoTo err1 ReDim Preserve tmp_array(UBound(tmp_array) + 1) On Error GoTo 0 tmp_array(UBound(tmp_array)) = c.Address (False, False) End If Set c = .Range("A1").CurrentRegion.Columns (1).FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress On Error GoTo err2 For tmp = UBound(tmp_array) To 1 Step -1 .Range(tmp_array(tmp)).EntireRow.Delete Next End If End With Unload Me MsgBox "1 Record Deleted Successfully", vbOKOnly err2: Exit Sub err1: ReDim tmp_array(1) Resume Next End Sub Thanx, Todd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying Code that deletes a Row
Todd,
This code For tmp = UBound(tmp_array) To 1 Step -1 .Range(tmp_array(tmp)).EntireRow.Delete Next can be replaced by For tmp = UBound(tmp_array) To 1 Step -1 Range("K1") = .Range(tmp_array(tmp)).Row Next but it will get over-written if you find more than 1 occurence. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Hey guys Happy Thanksgiving. Here is a code(see below) that deletes the entire row if Textbox1,2,and3 match data found in a row in range A1:A3 starting in A1. What would the code be, that would put the number of the row to be deleted in cell K1, instead of actually deleting the entire Row that matches the criteria in the 3 textboxes? I dont want to delete the row, just put the row to be deleted in cell K1. Private Sub CommandButton6_Click() If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub End If Dim c Dim firstAddress As String, tmp As Integer Dim tmp_array() With Worksheets(2) Set c = .Range("A1").CurrentRegion.Columns(1).Find (TextBox1.Text, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do If c.Offset(0, 1).Value = TextBox2.Text And c.Offset(0, 2).Value = TextBox3.Text Then On Error GoTo err1 ReDim Preserve tmp_array(UBound(tmp_array) + 1) On Error GoTo 0 tmp_array(UBound(tmp_array)) = c.Address (False, False) End If Set c = .Range("A1").CurrentRegion.Columns (1).FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress On Error GoTo err2 For tmp = UBound(tmp_array) To 1 Step -1 .Range(tmp_array(tmp)).EntireRow.Delete Next End If End With Unload Me MsgBox "1 Record Deleted Successfully", vbOKOnly err2: Exit Sub err1: ReDim tmp_array(1) Resume Next End Sub Thanx, Todd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modifying Code that deletes a Row
Thank you
Todd -----Original Message----- Todd, This code For tmp = UBound(tmp_array) To 1 Step -1 .Range(tmp_array(tmp)).EntireRow.Delete Next can be replaced by For tmp = UBound(tmp_array) To 1 Step -1 Range("K1") = .Range(tmp_array(tmp)).Row Next but it will get over-written if you find more than 1 occurence. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Hey guys Happy Thanksgiving. Here is a code(see below) that deletes the entire row if Textbox1,2,and3 match data found in a row in range A1:A3 starting in A1. What would the code be, that would put the number of the row to be deleted in cell K1, instead of actually deleting the entire Row that matches the criteria in the 3 textboxes? I dont want to delete the row, just put the row to be deleted in cell K1. Private Sub CommandButton6_Click() If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub End If Dim c Dim firstAddress As String, tmp As Integer Dim tmp_array() With Worksheets(2) Set c = .Range("A1").CurrentRegion.Columns(1).Find (TextBox1.Text, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do If c.Offset(0, 1).Value = TextBox2.Text And c.Offset(0, 2).Value = TextBox3.Text Then On Error GoTo err1 ReDim Preserve tmp_array(UBound (tmp_array) + 1) On Error GoTo 0 tmp_array(UBound(tmp_array)) = c.Address (False, False) End If Set c = .Range("A1").CurrentRegion.Columns (1).FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress On Error GoTo err2 For tmp = UBound(tmp_array) To 1 Step -1 .Range(tmp_array(tmp)).EntireRow.Delete Next End If End With Unload Me MsgBox "1 Record Deleted Successfully", vbOKOnly err2: Exit Sub err1: ReDim tmp_array(1) Resume Next End Sub Thanx, Todd . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combination of / and 8 deletes the column | Excel Worksheet Functions | |||
Keystroke that Deletes Entire Row? | Excel Discussion (Misc queries) | |||
Macro that deletes every third row....+ | Excel Discussion (Misc queries) | |||
Detecting when a user deletes a row | Excel Worksheet Functions | |||
Repetative Row Deletes | Excel Discussion (Misc queries) |