Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort or filter or delete by strike-through?
Couple different ways to do this. You could use a function:
Function isStriked(cellTest As Range) As Boolean Application.Volatile If cellTest.Font.Strikethrough Then isStriked = True Else isStriked = False End If End Function Then, in a helper column, you could use something like =isStriked(A2). It will return the word True if A2 is striked and False if it isn't. Sort on the column, filter on the column, whatever. Or you could use a loop to cycle through a range of cells and delete the whole row if the cell in the range is striked. Sub deleteStrikes() Dim rNum As Long rNum = Cells(Rows.Count, 1).End(xlUp).Row For i = rNum To 2 Step -1 If Cells(i, 1).Font.Strikethrough Then _ Cells(i, 1).EntireRow.Delete Next i End Sub Annie1904 wrote: I've got a huge database in Excel, and many of the records have had text formatted with a strike-trhough. Initially, this was so I could track what had been 'deleted', but now I want to remove those records. Rather than go through them individually, is there a quicker way? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort or filter or delete by strike-through?
After reading Dave's post, I feel I better clarify the code I posted
earlier. The function and the sub will only work if the whole cell is formatted as strikethrough. JW wrote: Couple different ways to do this. You could use a function: Function isStriked(cellTest As Range) As Boolean Application.Volatile If cellTest.Font.Strikethrough Then isStriked = True Else isStriked = False End If End Function Then, in a helper column, you could use something like =isStriked(A2). It will return the word True if A2 is striked and False if it isn't. Sort on the column, filter on the column, whatever. Or you could use a loop to cycle through a range of cells and delete the whole row if the cell in the range is striked. Sub deleteStrikes() Dim rNum As Long rNum = Cells(Rows.Count, 1).End(xlUp).Row For i = rNum To 2 Step -1 If Cells(i, 1).Font.Strikethrough Then _ Cells(i, 1).EntireRow.Delete Next i End Sub Annie1904 wrote: I've got a huge database in Excel, and many of the records have had text formatted with a strike-trhough. Initially, this was so I could track what had been 'deleted', but now I want to remove those records. Rather than go through them individually, is there a quicker way? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort or filter or delete by strike-through?
But it wouldn't be difficult to modify based on what the OP really wants.
As written, the cell has to be formatted as strikethrough. And that could be what the OP really wants. JW wrote: After reading Dave's post, I feel I better clarify the code I posted earlier. The function and the sub will only work if the whole cell is formatted as strikethrough. JW wrote: Couple different ways to do this. You could use a function: Function isStriked(cellTest As Range) As Boolean Application.Volatile If cellTest.Font.Strikethrough Then isStriked = True Else isStriked = False End If End Function Then, in a helper column, you could use something like =isStriked(A2). It will return the word True if A2 is striked and False if it isn't. Sort on the column, filter on the column, whatever. Or you could use a loop to cycle through a range of cells and delete the whole row if the cell in the range is striked. Sub deleteStrikes() Dim rNum As Long rNum = Cells(Rows.Count, 1).End(xlUp).Row For i = rNum To 2 Step -1 If Cells(i, 1).Font.Strikethrough Then _ Cells(i, 1).EntireRow.Delete Next i End Sub Annie1904 wrote: I've got a huge database in Excel, and many of the records have had text formatted with a strike-trhough. Initially, this was so I could track what had been 'deleted', but now I want to remove those records. Rather than go through them individually, is there a quicker way? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I sort or filter or delete by strike-through? | Excel Discussion (Misc queries) | |||
AFTER 8 SEC ON SPACEBAR I GOT A FILTER?DELETE IT? | Excel Discussion (Misc queries) | |||
Filter list and delete | Excel Discussion (Misc queries) | |||
filter and delete | Excel Worksheet Functions | |||
Fastest Way to Filter/Delete | Excel Worksheet Functions |