Skip i'r prif gynnwys

Sut i dynnu neu ddileu pob testun neu gymeriad trawiadol o gelloedd yn Excel?

Mae'r erthygl hon yn sôn am gael gwared ar yr holl destunau neu gymeriadau trawiadol o gelloedd lluosog yn Excel.

Tynnwch yr holl destunau trawiadol o gelloedd sydd â chod VBA


Tynnwch yr holl destunau trawiadol o gelloedd sydd â chod VBA

Gall y cod VBA isod eich helpu i gael gwared ar yr holl destunau mewn celloedd sydd wedi'u marcio â streic. Gwnewch fel a ganlyn.

1. Yn y daflen waith mae'n cynnwys y testunau trawiadol y mae angen i chi eu tynnu, pwyswch y Alt + F11 allweddi ar yr un pryd i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

2. Yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, cliciwch HMS > Modiwlau. Yna copïwch a gludwch islaw cod VBA i mewn i ffenestr y Cod.

Cod VBA: Tynnwch yr holl destunau trawiadol o gelloedd

Sub DelStrikethroughText()
    Dim xRg As Range, xCell As Range
    Dim xStr As String
    Dim I As Long
    On Error Resume Next
    Set xRg = Application.InputBox("Please select range:", "KuTools For Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Application.ScreenUpdating = Fase
        For Each xCell In xRg
            If IsNumeric(xCell.Value) And xCell.Font.Strikethrough Then
                xCell.Value = ""
            ElseIf Not IsNumeric(xCell.Value) Then
                For I = 1 To Len(xCell)
                    With xCell.Characters(I, 1)
                        If Not .Font.Strikethrough Then
                            xStr = xStr & .Text
                        End If
                    End With
                Next
                xCell.Value = xStr
                xStr = ""
            End If
        Next
    Application.ScreenUpdating = True
End Sub

3. Gwasgwch y F5 allwedd neu cliciwch y Run botwm i redeg y cod. Yn y popping up Kutools ar gyfer Excel blwch deialog, dewiswch yr ystod sy'n cynnwys y testunau trawiadol y byddwch chi'n eu tynnu, ac yna cliciwch ar y OK botwm. Gweler y screenshot:

Yna mae'r holl destunau sydd wedi'u marcio â streic yn cael eu tynnu o'r celloedd yn syth fel y dangosir y llun isod.


Erthyglau cysylltiedig:

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog    VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr    Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad


Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (10)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hey guys, this worked for me but in some cases it somehow changes the text which is not formatted as strikethrough as strikethrough. Easy fix to go back and spotcheck the data in specific instances and un-strikethrough the range.
This comment was minimized by the moderator on the site
Hi,Which Excel version are you using?
This comment was minimized by the moderator on the site
I use it for the whole column and my excel never come back....
This comment was minimized by the moderator on the site
Hi san,
Would you provide the Excel version you are using?
The code works well in my case.
Sorry for the inconvenience.
This comment was minimized by the moderator on the site
can it be done through python, pandas ?
If yes can anybody help me out how to implement this ?
This comment was minimized by the moderator on the site
Hi Girish,
The code hasn't been tested in python, pandas. Sorry can't help you with that. Thanks for commenting.
This comment was minimized by the moderator on the site
This does not work. Excel goes into Not Responding.
This comment was minimized by the moderator on the site
It works in small scale. I assume that the reason it goes into Not Responding is because the Macro takes forever when you have a lot of data to get through. This Macro is checking every single character for strike through.

I would recommend adding code that reformats the cell when it is done because I had several cells where the code removed the strikethrough text but the remaining text was still in strikethrough. I believe this is because the formatting of the full cell was strikethrough with the non strikethrough text being formatted differently, so just an FYI.
This comment was minimized by the moderator on the site
Good day,
Sorry for the inconvenience. Would you provide your Office version? Thanks for your comment.
This comment was minimized by the moderator on the site
Same here.

I am using Office 365 ProPlus
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations