使用vba删除范围内的删除线单元格 [英] delete the strikethrough cells in a range using vba
问题描述
我在一个工作表中有500行和大约13列数据.
I have 500 rows and around 13 columns of data in a sheet.
即使单元格包含所有字符作为删除线,我也需要删除单元格内容本身,但是,如果该单元格包含某些文本和删除线的组合,则应单独删除删除线并保留其余文本在单元格中.
I need to delete the cell contents itself even if the cell contains all the characters as strike-through, but if the cell contains combination of some text and strike-through it should delete the strike-through alone and leave the remaining text in the cell.
这就是我的专业水平
A B C D E F G H I J K L M
1.2 SERVER_P RE1 **GR5**
7.3 PROXY NET
4.5 NET **CON** V1 GR
如果我希望**内的文本是删除线,则第一行L列应为空,第三行应删除CON,因此应保留为"NET V1".
If text inside ** are strike-through I expect, in 1st row column L should be empty and in 3rd row it should delete CON , so it should remain "NET V1".
这是我到目前为止所拥有的
Here is what I have till now
Dim Cell As Range
Dim iCh As Integer
Dim NewText As String
Sheets("Copy_indications").Select
With ActiveSheet
'count the rows till which strings are there
Lrow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
For Each Cell In Range("B1:M" & Lrow)
For iCh = 1 To Len(Cell)
With Cell.Characters(iCh, 1)
If .Font.Strikethrough = False Then
NewText = NewText & .Text
End If
End With
Next iCh
NewText = Cell.Value
Cell.Characters.Font.Strikethrough = False
Next Cell
如果单元格包含一些文本和删除线的组合,我的宏将删除所有删除线字符,但是如果该单元格包含所有字符作为删除线,则它不会删除它们,而是从它们中删除删除线
My macro deletes all the strike-through characters if the cell contains combination of some text and strike-through, but if the cell contains all the characters as strike-through, it does not delete them instead it removes the strike from them.
有人可以帮我吗?
推荐答案
好的解决方案,只是更正了一些错误(请参见代码中的注释)
Good solution, just corrected a few mistakes (see comments in the code)
Dim Cell As Range, iCh As Integer, NewText As String
With Sheets("Copy_indications") ' <~~ avoid select as much as possible, work directly with the objects
Lrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For Each Cell In .Range("B1:M" & Lrow)
For iCh = 1 To Len(Cell)
With Cell.Characters(iCh, 1)
If .Font.Strikethrough = False Then NewText = NewText & .Text
End With
Next iCh
Cell.Value = NewText ' <~~ You were doing it the other way around
NewText = "" ' <~~ reset it for the next iteration
Cell.Characters.Font.Strikethrough = False
Next Cell
End With
这篇关于使用vba删除范围内的删除线单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!