Delete rows if

[Giancar]

Hi everyone,

Which is the VBA code to delete in the Active worksheet, all the rows that contains in Column D words in strikethrough format?
Weird thing: when I try to find words strikethrough format through "find and replace" button, I have no results.
I am really struggling.

Thank you

 

[Fluff]

Give this a go

Sub DeletRowWithStrikethrough()
   With Columns("D:D")
      With .Application.FindFormat
         .Clear
         .Font.Strikethrough = True
      End With
      .Replace "", "", xlPart, , , , True, False
      .Application.FindFormat.Clear
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

It will only work if the entire cell has strikethrough. Also it will delete any rows where the cell in col D is blank. If that won't work for you let me know

 

[Giancar]

Give this a go

Sub DeletRowWithStrikethrough()
   With Columns("D:D")
      With .Application.FindFormat
         .Clear
         .Font.Strikethrough = True
      End With
      .Replace "", "", xlPart, , , , True, False
      .Application.FindFormat.Clear
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
End Sub

It will only work if the entire cell has strikethrough. Also it will delete any rows where the cell in col D is blank. If that won't work for you let me know

Hey, you helped me a lot, but still not the result I wished.
This code delete the empty rows (something that I do not need, but it is fine as I never have empty rows) but it seems not affecting the strikethrough rows.
The worksheet it is a google sheet doc converted in a Excel doc, so not sure that is strikethrough in that way (not sure I am clear).
Thank you

 

[Fluff]

If it's been converted, that may well be the problem.
As I've never used Google Sheets, I'm unable to offer any further help.

 

[Rick Rothstein]

The worksheet it is a google sheet doc converted in a Excel doc, so not sure that is strikethrough in that way (not sure I am clear).

Select a cell with what you think is strikethrough font, press CTRL+1 to bring up the "Format Cells" dialog box, select the "Font" tab and then look at the "Effects" panel... is there a check mark in the "Strikethrough" checkbox?

 

[Giancar]

Select a cell with what you think is strikethrough font, press CTRL+1 to bring up the "Format Cells" dialog box, select the "Font" tab and then look at the "Effects" panel... is there a check mark in the "Strikethrough" checkbox?

No, it's not selected. Can we find an alternative way?
Basically the Column D is "strikethrough" if Column F is before today (but then I think I have a different problem because the date is in this format: 19/12/2017 04:59:00)

I am really desperate...too much for my skills

 

[Fluff]

Assuming that this is connected to this https://www.mrexcel.com/forum/excel...leting-rows-multiple-sheet-2.html#post4973186
Try

Sub DelRows()

   Dim UsdRws As Long
   Dim Ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    
   For Each Ws In Sheets(Array("FR", "IT", "ES"))
      If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
      UsdRws = Ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
      Ws.Range("A2:AR2").AutoFilter Field:=6, Criteria1:="<" & CLng(Date)
      Ws.Range("F3:F" & UsdRws).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      Ws.ShowAllData
   Next Ws
   
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Rick Rothstein]

No, it's not selected. Can we find an alternative way?
Basically the Column D is "strikethrough" if Column F is before today (but then I think I have a different problem because the date is in this format: 19/12/2017 04:59:00)

Ahh, your strikethrough is the result of Conditional Formatting then, correct? If so, that is why you cannot see the font as being strikethough... Conditional Formatting is not "real" formatting per se, rather, it is variable formatting that gets overlaid on top of the cells real format. See if the following does what you want...

Sub DeleteStrikeThroughRows()
  Dim Cell As Range
  For Each Cell In Range("D1", Cells(Rows.Count, "D").End(xlUp))
    If Cell.DisplayFormat.Font.Strikethrough Then Cell.Value = "#N/A"
  Next
  On Error Resume Next
  Columns("D").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

 

[Fluff]

Nice spot Rick, I was wondering how it got there.

 

[Giancar]

Assuming that this is connected to this https://www.mrexcel.com/forum/excel...leting-rows-multiple-sheet-2.html#post4973186
Try

Sub DelRows()

   Dim UsdRws As Long
   Dim Ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    
   For Each Ws In Sheets(Array("FR", "IT", "ES"))
      If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
      UsdRws = Ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
      Ws.Range("A2:AR2").AutoFilter Field:=6, Criteria1:="<" & CLng(Date)
      Ws.Range("F3:F" & UsdRws).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      Ws.ShowAllData
   Next Ws
   
Application.Calculation = xlCalculationAutomatic
End Sub

This is working!!! So at the end you worked on the date! Thanks
Just a quick top up on your code: is it possible to delete also rows that contain "#N/A" in column D?

That is a Gdoc that has been created by someone else following their rules, nothing that I can change