Excel: Code to delete entire rows when font is Strikethrough?

[Keith]

I have a spreadsheet where some rows are formatted with strikethrough font.
I need some code to delete the entire row where the font is set to strike
through.

Thanks,

Keith

 

[OssieMac]

Hi Keith,

Are the entire rows formatted to strikethrough? If so then the following
code should do what you want. If not the entire row formatted to
strikethrouth then will need to know the specific columns that are.

Note: It is necessary to work backwards from the bottom when deleting rows.

Sub DeleteStrikeThrough()
Dim rngRow As Range
Dim r As Long

For r = Sheets("Sheet1").UsedRange.Rows.Count To 1 Step -1
If Rows(r).Font.Strikethrough = True Then
Rows(r).Delete
End If
Next r

End Sub

 

[OssieMac]

Hi again Keith,

If not the entire row, if you have a specific column that will have the
strickthrough then the following will do what you want. Just replace the "A"
in Cells(r,"A") with whatever column you wish.

Also in the previous post, delete the line Dim rngRow As Range. I initially
was going to do it a different way and didn't remove the line.

Sub DeleteStrikeThrough2()
Dim r As Long

For r = Sheets("Sheet1").UsedRange.Rows.Count To 1 Step -1
If Cells(r, "A").Font.Strikethrough = True Then
Cells(r, "A").EntireRow.Delete
End If
Next r

End Sub

 

[Keith]

Hello OssieMac,

Thanks for the quick response,

The entire row is not formatted as strike through, only columns A to M.
Could we just base it on Column A?

 

[OssieMac]

Obviously you posted this before my second post showed up which does base it
on column A.

 

[Keith]

Thanks OsieMac,

It works well.
Does just what I need.


Regards,

Keith

 

[Dana DeLouis]

Hi. Just a note if it applies. This assumes data is in Row 1.

For r = Sheets("Sheet1").UsedRange.Rows.Count To 1 Step -1

For example, if you only have data in A5:B7, then there are 3 rows, and
not the assumed 7.

Sub DeleteStrikeThrough2()
Dim r As Long

For r = Sheets("Sheet1").UsedRange.Rows.Count To 1 Step -1
Debug.Print r
Next r
End Sub

This returns 3,2,1 and not 7,6,5,4,3,2,1
Again, just a heads-up.
- - -
Dana DeLouis

 

[Kashyap]

I have applied conditional formating in column k asking to Strikethrough if
condition is met, but the below macro is not not working with conditional
formating.. Pls help