PDA

View Full Version : A way to filter based on strikethrough formatting?



snickerbe3
03-11-2016, 02:25 PM
Hello,
I am using Excel 2010. I have a file with data in columns A to AB and about 15,000 rows of data. Any one or more cells per row may have strikethrough formatting applied. I want to identify all records with any strikethrough format applied, whether to one cell in the row or all cells in the row. My end goal is to separate any row with strikethrough formatting to move these rows to a different worksheet. Any advice on how to accomplish this will be greatly appreciated!

Thank you in advance for your time!!

mancubus
03-12-2016, 08:02 AM
welcome to vbax.

try:


Sub vbax_55413_filter_copy_strikethrough_formatted_cells()


Dim i As Long, j As Long

With Worksheets("SourceSheet") 'change SourceSheet to suit
.AutoFilterMode = False
.Cells(1, 29).Value = "Strikethrough?" 'AC1
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
For j = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
If .Cells(i, j).Font.Strikethrough = True Then
.Cells(i, 29).Value = "Yes"
Exit For
End If
Next j
Next i
.Cells(1).AutoFilter Field:=29, Criteria1:="=Yes"
.AutoFilter.Range.Resize(, 28).Copy Worksheets("DestinationSheet").Cells(1) 'change DestinationSheet to suit
.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
.Columns(29).Clear
End With


End Sub