none
VBA Delete Rows - Based on Strings found in Column RRS feed

  • Question

  • Hi friends,

     

    I am trying to delete some rows and have got stuck.

     

    I have listed the rows that contain a string in a worksheet.

     

    I am trying to delete rows in another sheet that contains the string found in the DeleteRowsList worksheet

    Sub DeleteRows()
    
    
    
        Dim ws     As Worksheet
        Dim i      As Integer
        Dim oSheets As Worksheet
    
        Set ws = Worksheets("DeleteRowsList")
        For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row     ' Rows to delete
            
            Set oSheets = Worksheets("Sheet1")
        
            
            oSheets.Rows(ws.Cells(i, "A").Value).Delete
            
        Next i
    
        
    End Sub
    

    I think I am confused with the logic and order :(

    please advise


    Cheers Dan :)


    Tuesday, November 1, 2016 7:46 PM

Answers

  • Here you go.. try the below code and let me know if this helps..

    Here I am assuming that In Sheet1, you are looking for these words in Column 1 from Row 2 onwards. You can change them accordingly in the below code:

    Sub DeleteRows()
    
    
        Dim ws     As Worksheet
        Dim i      As Integer
        Dim oSheets As Worksheet
        Dim itemToFind
    
        Set ws = Worksheets("DeleteRowsList")
        For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row     ' Rows to delete
            itemToFind = ws.Cells(i, 1).Value
            Worksheets("Sheet1").Activate
            With ActiveSheet
                .Rows("1:1").Select
                Selection.AutoFilter
                Range("A1").Select
                Selection.AutoFilter Field:=1, Criteria1:=itemToFind
                .UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Rows.Delete
                Selection.AutoFilter
            End With
           
        Next i
    
    End Sub


    Vish Mishra

    • Marked as answer by Dan_CS Tuesday, November 1, 2016 11:08 PM
    Tuesday, November 1, 2016 8:51 PM

All replies

  • Hi,

    With your VBA code, it is not clear like what are you comparing to delete the row. 

    Can you explain about it more in detail?

    regards,

    Vishwa


    Vish Mishra

    Tuesday, November 1, 2016 8:05 PM
  • Hi Vishwa,

    in 1 worksheet i have listed all the rows that contain the words to delete.

    Delete the Rows in Sheet1


    Cheers Dan :)

    Tuesday, November 1, 2016 8:13 PM
  • Here you go.. try the below code and let me know if this helps..

    Here I am assuming that In Sheet1, you are looking for these words in Column 1 from Row 2 onwards. You can change them accordingly in the below code:

    Sub DeleteRows()
    
    
        Dim ws     As Worksheet
        Dim i      As Integer
        Dim oSheets As Worksheet
        Dim itemToFind
    
        Set ws = Worksheets("DeleteRowsList")
        For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row     ' Rows to delete
            itemToFind = ws.Cells(i, 1).Value
            Worksheets("Sheet1").Activate
            With ActiveSheet
                .Rows("1:1").Select
                Selection.AutoFilter
                Range("A1").Select
                Selection.AutoFilter Field:=1, Criteria1:=itemToFind
                .UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).Rows.Delete
                Selection.AutoFilter
            End With
           
        Next i
    
    End Sub


    Vish Mishra

    • Marked as answer by Dan_CS Tuesday, November 1, 2016 11:08 PM
    Tuesday, November 1, 2016 8:51 PM
  • Hi Vish,

    thank you very much for your kind help and speedy response. :)

    Something happened and then it didnt.

    I am getting an error on  Selection.AutoFilter

    Something about cant select a range

    here is the test

    I have not changed the code - juts the name of the sheet to Test

    thanks again


    Cheers Dan :)

    Tuesday, November 1, 2016 9:15 PM
  • Hi,

    Yes I can see that... You do not have any header in A1. If you are not going to have any header then please change the selection from Row 2 and not from row 1. 

    If you just put any value in A1, it will work


    Vish Mishra

    Tuesday, November 1, 2016 9:51 PM
  • Hello Vish,

    oops  as you can see I am a bit not on the very techy side with my paying attention to detail.

    Thank you very much for helping it worked now yay :)

    Have a great evening now


    Cheers Dan :)

    Tuesday, November 1, 2016 11:08 PM