Deleting Rows by Font

hamptongolfer11 · Jan 6th 2022

Hi, below is a code I use to delete entire row when a blank cell is found in column M. It works great! I would like to know if I can modify this and use it in a separate macro so that I can delete an entire row when a certain font is found in Column B. I know I can custom sort by font color. I have some names in Column B that are shaded a slightly lighter color and want to remove them after sorting. Thanks.

Code

Sub Delete_Blank_Rows()





On Error Resume Next



Range("M7:M151").Select



Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete



End Sub

Display More

Mumps · Jan 6th 2022

Could you attach a copy of your file? It would be easier to see how your data is organized and to test possible solutions. Include a detailed explanation

of what you want to do using a few examples from your data and referring to specific cells, rows, columns and sheets. De-sensitize the data if necessary.

hamptongolfer11 · Jan 6th 2022

Here is an example of my sheet. In column B, Player10, Player20,.......to.......Player 120 are in a slightly lighter font. I would like to remove the rows where there are players in the lighter font, resort the sheet by column x, lowest to highest, remove any blank rows, renumber the list in column A and have the chart below show up on the 2nd row below the last player. In reality, the lighter fonts will not be in every 10th spot but randomly within the list. Also each week, there may be more or less players in this font. I have a macro that will sort and move all the lighter fonts to the bottom of the sheet where I can remove them but if I can accomplish it in one stroke, it would be better. Thanks again.

Mumps · Jan 7th 2022

In the file you posted, I don't see any colour formatting for any player in column B. The font formatting is all "Automatic" (black). Could you please explain what you mean by:

Quote

Player10, Player20,.......to.......Player 120 are in a slightly lighter font

hamptongolfer11 · Jan 7th 2022

Quote from Mumps

In the file you posted, I don't see any colour formatting for any player in column B. The font formatting is all "Automatic" (black). Could you please explain what you mean by:

Hi, in Column B, cells 10,20,30,40,50,60,70,80,90,100,110 & 120 are all 1 shade lighter than black. If you custom sort by Column B, Font Color, they will all come to the top. I do this so the names don't stand out from the others when I send it out to the members.

Mumps · Jan 7th 2022

Try:

Code

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:AD" & LastRow).AutoFilter Field:=2, Criteria1:=RGB(38, 38, 38), Operator:=xlFilterFontColor
    ActiveSheet.AutoFilter.Range.EntireRow.Delete
    Range("A1").AutoFilter
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("X7:X" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A6:AD" & LastRow)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    With Range("A6")
        .Value = "1"
        .AutoFill Destination:=Range("A6").Resize(LastRow - 5), Type:=xlFillSeries
    End With
    Application.ScreenUpdating = True
End Sub

Display More

hamptongolfer11 · Jan 7th 2022

Fantastic! Works perfectly. Thanks Mumps.

Mumps · Jan 7th 2022

You are very welcome.

Deleting Rows by Font

Participate now!

Share