Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETING ROW
hI THERE!I'd like to ask help on how to delete row based on a value entered
in a textbox located in a user form.The userform and the worksheet where the data to be deleted is stored are located in the same workbook. Example: After a user input a four digit number it will automatically search in a specific column of a particular sheet of a workbook. If a match was found the data will be displayed in there respective control of the form and when an OKcommand button is clicked the found data will be deleted. Pls.. Help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETING ROW
This will work. It needs modified if the value could be found more
than once and you want to delete all instances. Dim miRow As Long Private Sub CommandButton1_Click() If miRow = 0 Then Exit Sub If Label2 = "Found!" Then Sheets("Sheet1").Rows(miRow).Delete Label2 = "" End If miRow = 0 End Sub Private Sub TextBox1_AfterUpdate() Dim iFind As Integer iFind = TextBox1.Value Do miRow = miRow + 1 If iFind = Sheets("Sheet1").Range("A" & miRow) Then Label2.Caption = "Found!" Exit Sub End If Loop Until Sheets("Sheet1").Range("A" & iRow + 1) = "" Label2 = "Not found." End Sub Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETING ROW
Public cell as Range
Private Sub TextBox1_Change() Dim rng as Range, rng1 as Range Dim s as String if len(Textbox1.Value) = 4 then if isnumeric(Textbox1.Value) then set cell = Nothing Textbox2.value = "" Textbox3.Value = "" s = Textbox1.Value set rng = Worksheets("Data").Range("C:C") Set rng1 = rng.Find(What:=s, _ After:=rng(rng.count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then set cell = rng1 Textbox2.value = cell.offset(0,-2) Textbox3.Value = cell.offset(0,-1) end if end if end if End Sub Private Sub CommandButton1_Click() if not cell is nothing then if len(trim(textbox1.value)) = 4 then Textbox1.Value = "" Textbox2.Value = "" Textbox3.Value = "" cell.EntireRow.Delete set cell = Nothing end if end if end Sub -- Regards, Tom Ogilvy "Chin x21" wrote: hI THERE!I'd like to ask help on how to delete row based on a value entered in a textbox located in a user form.The userform and the worksheet where the data to be deleted is stored are located in the same workbook. Example: After a user input a four digit number it will automatically search in a specific column of a particular sheet of a workbook. If a match was found the data will be displayed in there respective control of the form and when an OKcommand button is clicked the found data will be deleted. Pls.. Help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DELETING ROW
Thanks Tom! You got what I mean.. I've been waiting for your response cause I find it easy to understood. "Tom Ogilvy" wrote: Public cell as Range Private Sub TextBox1_Change() Dim rng as Range, rng1 as Range Dim s as String if len(Textbox1.Value) = 4 then if isnumeric(Textbox1.Value) then set cell = Nothing Textbox2.value = "" Textbox3.Value = "" s = Textbox1.Value set rng = Worksheets("Data").Range("C:C") Set rng1 = rng.Find(What:=s, _ After:=rng(rng.count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then set cell = rng1 Textbox2.value = cell.offset(0,-2) Textbox3.Value = cell.offset(0,-1) end if end if end if End Sub Private Sub CommandButton1_Click() if not cell is nothing then if len(trim(textbox1.value)) = 4 then Textbox1.Value = "" Textbox2.Value = "" Textbox3.Value = "" cell.EntireRow.Delete set cell = Nothing end if end if end Sub -- Regards, Tom Ogilvy "Chin x21" wrote: hI THERE!I'd like to ask help on how to delete row based on a value entered in a textbox located in a user form.The userform and the worksheet where the data to be deleted is stored are located in the same workbook. Example: After a user input a four digit number it will automatically search in a specific column of a particular sheet of a workbook. If a match was found the data will be displayed in there respective control of the form and when an OKcommand button is clicked the found data will be deleted. Pls.. Help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting the text without deleting the formula | Excel Worksheet Functions | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |