• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete in yellow color row in all sheet through VBA

I have try to Delete in yellow color row in all sheet through VBA (Excel version 2013)

eg.

Sheet Name is . . . "A Team", "B Team" & only "C Team"

in all the sheet, we have highlight the yellow color, due to delete the row.

I want delete in all the sheet the yellow color row only, then automatically to be create the new sheet name is "Delete list" in same excel file

and then deleted yellow color row list wise with sheet name in "A column" come to "Delete list" sheet.
 

Attachments

  • Book1.xlsx
    11.2 KB · Views: 8
According to the attachment a starter demonstration to paste to the (Delete List) worksheet module :​
Code:
Sub Demo1()
    Dim R&, W%, L&
        Me.UsedRange.Offset(1).Clear
        Application.ScreenUpdating = False
        R = 2
    For W = 1 To Me.Index - 1
        With Worksheets(W).[A3].CurrentRegion
              .AutoFilter 1, vbYellow, xlFilterCellColor
               L = Application.Subtotal(103, .Columns(1)) - 1
            If L Then
                 Cells(R, 1).Resize(L).Value2 = .Parent.Name
                .Offset(1).Copy Cells(R, 2)
                .Offset(1).Rows.Delete xlShiftUp
                 R = R + L
            End If
              .AutoFilter
        End With
    Next
        If R > 2 Then Range("A2:A" & R - 1).Borders.Weight = xlThin
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Hi, i try the above code, but i got the error.
 

Attachments

  • 1572506570216.png
    1572506570216.png
    156.7 KB · Views: 4
  • Book1 (1).xlsm
    16.4 KB · Views: 1
Last edited by a moderator:
Hi,

I want delete in all the sheet the yellow color row only,

Hereafter automatically to be create "Delete Data" sheet name same excel file.

All yellow color data list to be come in "Delete Data" sheet.

Just done.
 

Attachments

  • Book1 (1).xlsx
    11.3 KB · Views: 0
Last edited:
Back
Top