none
Delete rows based on a userform textbox value Excel 2010

    Question

  • Hello all, this seems simple but can't figure it out, I want to delete rows on sheets 2 and 3 that match my textbox value. The code below works on sheet 3 if I replace "Me.txtProbOppTblID.Value" with a ID number like "If rng.Cells(i).Value = 2". It's a one too many relationship. So for each problem there can be many objectives.  So what I want is if the user deletes the problem all related objectives go with.

    Private Sub CommandButton1_Click()
       Dim rng As Range, i As Integer
            'Set the range to evaluate to rng.
       Set rng = Sheets(3).Range("H2:H5")
            'Loop backwards through the rows
           'in the range that you want to evaluate.
         For i = rng.Rows.Count To 1 Step -1
                
      If rng.Cells(i).Value = Me.txtProbOppTblID.Value Then rng.Cells(i).EntireRow.Delete
           Next
      
    End Sub
    • Edited by rixmcx59 Wednesday, July 10, 2013 8:58 PM
    Wednesday, July 10, 2013 8:06 PM

Answers

  • A text box returns a string - say, "2" - which is different from the number 2. What type of values exist on sheets 2 and 3 that you want to use for the removal of lines? Integers? 

    Try casting one or both to Integers:

    If rng.Cells(i).Value = CInt(Me.txtProbOppTblID.Value) then.....

    If CInt(rng.Cells(i).Value) = CInt(Me.txtProbOppTblID.Value) then.....

    • Marked as answer by rixmcx59 Thursday, July 11, 2013 4:02 PM
    Thursday, July 11, 2013 1:24 PM

All replies

  • A text box returns a string - say, "2" - which is different from the number 2. What type of values exist on sheets 2 and 3 that you want to use for the removal of lines? Integers? 

    Try casting one or both to Integers:

    If rng.Cells(i).Value = CInt(Me.txtProbOppTblID.Value) then.....

    If CInt(rng.Cells(i).Value) = CInt(Me.txtProbOppTblID.Value) then.....

    • Marked as answer by rixmcx59 Thursday, July 11, 2013 4:02 PM
    Thursday, July 11, 2013 1:24 PM
  • Bernie, that worked, thank you for the lesson in userform.
    Thursday, July 11, 2013 4:05 PM