none
Delete rows in a particular situation RRS feed

  • Question

  • hi 

    i wanna create a form in vba ( in Excel ) for deleting Row in a particular situation 

    in this form i wanna search a cell ( data ) in a 2 sheets and after that find it 

    every row contain this cell ( data ) deleted from my Workbook .... 

    can i Do it ? 

    is it possible ? 

    please Help me ... 

    Monday, November 26, 2012 7:46 PM

All replies

  • Your question is rather broad. Please provide more detailed information.

    Regards, Hans Vogelaar

    Monday, November 26, 2012 7:53 PM
  • i wanna delete all row contain a value or text (that i enter for example in a textbox ) from all sheets in my workbook .... 


    Monday, November 26, 2012 7:57 PM
  • Thanks, but please provide specific information. Should the code look in one column only, or throughout the entire sheet?

    Regards, Hans Vogelaar

    Monday, November 26, 2012 8:01 PM
  • in each sheet code look in one column 

    for example 

    sheet 1       look in     A1:A1000

    sheet 2       look in     C1:C1000

    if find value in these  columns delete row in each sheet 

    Monday, November 26, 2012 8:07 PM
  • Perhaps something like this. The code is intended for a command button named cmdDelete on a userform; it takes a value from a text box named txtSearch.

    Private Sub cmdDelete_Click()
        If Me.txtSearch = "" Then
            Me.txtSearch.SetFocus
            MsgBox "Please enter a value to look for, then try again", vbCritical
            Exit Sub
        End If
        DeleteFrom Worksheets("Sheet1"), "A", Me.txtSearch.Text
        DeleteFrom Worksheets("Sheet2"), "C", Me.txtSearch.Text
    End Sub
    
    Private Sub DeleteFrom(wsh As Worksheet, col As String, txt As String)
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = wsh.Range(col & wsh.Rows.Count).End(xlUp).Row
        For r = m To 1 Step -1
            If wsh.Range(col & r).Value = txt Then
                wsh.Range(col & r).EntireRow.Delete
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    Monday, November 26, 2012 8:18 PM
  • This is exactly what I want

    Thanks Hans Vogelaar

    Monday, November 26, 2012 8:30 PM