locked
Delete row where certain conditions are met RRS feed

  • Question

  • Hello,

    I have a robot file that I import excel workbook.

    The file looks around. like this:

    LabwareId Aspir_volume
    outRE585-017 15
    outRE585-017400
    outRE585-017405
    outRE585-017 15
    outRE585-017400
    outRE585-017405
    Liconic 15
    Liconic 400
    Liconic 405
    Liconic 15
    Liconic 400
    Liconic 405

    What I want is to use vba to delete all cells containing "liconic", 400 and 405
    how can i do that?

    sincerely,
    Geir Arne
    Monday, December 9, 2013 11:46 AM

Answers

  • Sub Loop_Example()
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
    
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    
        'We use the ActiveSheet but you can replace this with
        'Sheets("MySheet")if you want
        With ActiveSheet
    
            'We select the sheet so we can change the window view
            .Select
    
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
    
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
    
            'Set the first and last row to loop through
            Firstrow = .UsedRange.Cells(1).Row
            Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
    
                'We check the values in the A column in this example
                With .Cells(Lrow, "C")
    
                    If Not IsError(.Value) Then
    
                        If .Value = "Liconic" Then .EntireRow.Delete
                        'This will delete each row with the Value "
    
                        'in Column A, case sensitive.
    
                    End If
    
                End With
                With .Cells(Lrow, "J")
    
                    If Not IsError(.Value) Then
    
                        If .Value = "450" Or .Value = "400" Then .EntireRow.Delete
                        'This will delete each row with the Value "450 or 400"
                        'in Column A, case sensitive.
    
                    End If
    
                End With
    
            Next Lrow
    
        End With
    
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    
    End Sub
    

    case sensitive.

    Tuesday, December 10, 2013 5:39 PM

All replies

  • Hello Geir,

    The Range class from the Excel Object Model provides the Text , Value , Clear and Delete methods. So, you need to check out the Text or Value property and then call the clear or delete method.

    Monday, December 9, 2013 12:57 PM
  • Sub DeleteLiconic()
         'step 6
        '
        Dim FirstRow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
    
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    
        'We use the ActiveSheet but you can replace this with
        'Sheets("MySheet")if you want
        With ActiveSheet
    
            'We select the sheet so we can change the window view
            .Select
    
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
    
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
    
            'Set the first and last row to loop through
            FirstRow = .UsedRange.Cells(2).Row
            Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
    
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To FirstRow Step -1
    
                'Check the values in column A
                If Not IsError(.Cells(Lrow, "A").Value)  Then          'change the colum "A" for the column needed.
                
    
                    If (.Cells(Lrow, "A").Value = "Liconic 400") or (.Cells(Lrow, "A").Value = "Liconic 450")  Then  
                        .Cells(Lrow, "A").Value = ""   'this will clear the cell.
                    End If
    
                End If
                
    
            Next Lrow
    
        End With
    
    
    
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    
    
    End Sub
    this code will delete all cells (clear it) when it is "Liconic 400" or 405.  This code assume that it is in column A.  If you have it in multiple column, just make another macro and change the column.  So basically jsute change "A" to "B" if it in in column B.

    Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

    Monday, December 9, 2013 1:16 PM
  • Hello,

    Sorry I explained wrong. When I open robot file there are several cells of information. Liconic is in cell C (heading LabwareId) and number 400/450 is in cell J (heading Aspir_volume). I want to delete all rows with the word liconic in cell C and all rows containing the numbers 400/450 in cell J

    sincerely,
    Geir Arne

    Tuesday, December 10, 2013 4:27 PM
  • Sub Loop_Example()
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
    
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    
        'We use the ActiveSheet but you can replace this with
        'Sheets("MySheet")if you want
        With ActiveSheet
    
            'We select the sheet so we can change the window view
            .Select
    
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
    
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
    
            'Set the first and last row to loop through
            Firstrow = .UsedRange.Cells(1).Row
            Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
    
                'We check the values in the A column in this example
                With .Cells(Lrow, "C")
    
                    If Not IsError(.Value) Then
    
                        If .Value = "Liconic" Then .EntireRow.Delete
                        'This will delete each row with the Value "
    
                        'in Column A, case sensitive.
    
                    End If
    
                End With
                With .Cells(Lrow, "J")
    
                    If Not IsError(.Value) Then
    
                        If .Value = "450" Or .Value = "400" Then .EntireRow.Delete
                        'This will delete each row with the Value "450 or 400"
                        'in Column A, case sensitive.
    
                    End If
    
                End With
    
            Next Lrow
    
        End With
    
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    
    End Sub
    

    case sensitive.

    Tuesday, December 10, 2013 5:39 PM