Skip to main content

 Conas cnaipe a chur i bhfeidhm chun cealla ar leith a ghlanadh in Excel?

De ghnáth, is féidir leat an Ctrl eochair chun ilchealla sonracha a roghnú, agus ansin ábhar na gceall a ghlanadh de réir mar is gá duit. Más gá duit na cealla sonracha seo a ghlanadh ó am go ham, is féidir leat cnaipe soiléir a chruthú chun iad a ghlanadh le cliceáil amháin. San alt seo, labhróidh mé faoi conas cnaipe soiléir uile a chruthú chun roinnt ábhar sonrach cille a ghlanadh.

Cuir cnaipe i bhfeidhm chun ábhar sonrach cille a ghlanadh le cód VBA

mboilgeog cheart gorm saighead Cuir cnaipe i bhfeidhm chun ábhar sonrach cille a ghlanadh le cód VBA

Ar dtús, ba cheart duit cnaipe cruth a chruthú, agus ansin cód a chur i bhfeidhm, faoi dheireadh, an cód a cheangal leis an gcnaipe cruth. Déan mar a leanas le do thoil:

1. Cliceáil Ionsáigh > cruthanna > Dronuilleoga chun cruth na Dronuilleog a roghnú, agus ansin tarraing an luch chun cnaipe dronuilleog a tharraingt in áit ar bith den bhileog de réir mar a theastaíonn uait, féach an scáileán:

cnaipe doc cealla soiléire 1

2. Ansin ionchuir an téacs agus formáidigh an cnaipe cruth de réir mar a theastaíonn uait, féach an scáileán:

cnaipe doc cealla soiléire 2

3. Agus ansin ba chóir duit an cód VBA a chur isteach, coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fuinneog. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas sa Modúil Fuinneog.

Cód VBA: Ábhar soiléir cille ó chealla ar leith:

Sub Clearcells()
'Updateby Extendoffice
Range("A2", "A5").Clear
Range("C10", "D18").Clear
Range("B8", "B12").Clear
End Sub

nótaí: Sa chód thuas: A2, A5 tabhair le fios go nglanfaidh sé na cealla sa raon A2: A5, ní amháin an dá chealla ina n-aonar, is féidir leat raonta iolracha a chur leis, mar shampla Raon ("B8", "B12"). Glan script laistigh den chód le glanadh.

4. Ansin sábháil agus dún an fhuinneog cód, agus ansin an cód a nascadh leis an gcnaipe cruth, cliceáil ar dheis ar an gcnaipe, agus roghnaigh Macra a shannadh, I Macra a shannadh bosca dialóige, roghnaigh an Clearcells ainm cód ón Macra-ainm bosca liosta, agus cliceáil OK cnaipe chun an dialóg seo a fhágáil. Féach an pictiúr:

cnaipe doc cealla soiléire 3

5. Agus anois, nuair a chliceálann tú an Glan Gach cnaipe, glantar na cealla ar leith a shainigh tú ag an am céanna, féach an scáileán:

cnaipe doc cealla soiléire 4

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (73)
Rated 5 out of 5 · 1 ratings
El Roya
El Roya
This comment was minimized by the moderator on the site
is there a way to delete data from multiple sheets with just 1 button?
Reply
0
0
skyyang
skyyang     El Roya
This comment was minimized by the moderator on the site
Hello, Roya,
To clear the specific cells in multiple sheet, please apply the following vba code:
Sub ClearMultipleRanges()
    Dim ws As Worksheet
    Dim sheetsToClear As Variant
    Dim cellRanges As Variant
    Dim range As Variant

    sheetsToClear = Array("Sheet1", "Sheet2", "Sheet3") ' Change the sheet names
    cellRanges = Array("A1:C3", "E1:E5", "G1:G10") ' Modify the cell ranges
    For Each ws In ThisWorkbook.Sheets
        If Not IsError(Application.Match(ws.Name, sheetsToClear, 0)) Then
            For Each range In cellRanges
                ws.range(range).ClearContents
            Next range
        End If
    Next ws
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
El Roya
El Roya
This comment was minimized by the moderator on the site
is there a way to delete data from multiple sheets with just 1 button?
Reply
0
0
kenvel
kenvel
This comment was minimized by the moderator on the site
very helpful. thanks so much
Reply
0
0
David Leão
David Leão
This comment was minimized by the moderator on the site
Hi there,

Used this, and it works like a charm.

Only problem is... my cells are different formats... Some are 0,000 some are 00,00€ and when I it CLEAR, the format goes with tue clear event.

How can I clear the cells but maintain the format of them?
Reply
0
0
skyyang
skyyang     David Leão
This comment was minimized by the moderator on the site
Hello, David,
To keep the cell formatting when clearing the cell contents, please apply the below code:
Sub ClearCellsKeepFormat()
    'Update by Extendoffice
    'This version clears the contents but keeps the cell formatting intact.
    Range("A2:A5").ClearContents
    Range("C10:D18").ClearContents
    Range("B8:B12").ClearContents
End Sub


Please have a try, hope it can help you!
Reply
Report
0
0
David Leão
David Leão     skyyang
This comment was minimized by the moderator on the site
It worked. Thanks a lot man 😄
Reply
0
0
Rated 5 out of 5
Sabrina Gomez
Sabrina Gomez
This comment was minimized by the moderator on the site
Hi how do you use this macro without interfering the data validation?
Reply
0
0
Bharat Bali
Bharat Bali
This comment was minimized by the moderator on the site
I wanted that the confirmation needed for clear button .....so that the data is not cleared by mistakenly press the "clear" tab

And i wanted to say thanks for your brilliant work....thanks a lot in advance....

wish you have a great day...
Reply
0
0
Shannon
Shannon
This comment was minimized by the moderator on the site
Hello,
I am using this on one spreadsheet, but on 3 separate tabs. Everything was working last time I had the workbook open but today it is giving me an error "Ambiguous name detected: Clearcells". Please help!
Reply
0
0
Yac23
Yac23
This comment was minimized by the moderator on the site
Hallo zusammen,
hab soweit alles gemacht zu gewiesen usw. Funktioniert auch aber sobald ich die Datei öffne funktioniert es nicht mehr und muss alles neu eingeben.

Vorab vielen Dank
Reply
0
0
Yac23
Yac23     Yac23
This comment was minimized by the moderator on the site
Meinte, nach dem schließen und dann wieder öffne.
Reply
0
0
Leslie
Leslie
This comment was minimized by the moderator on the site
Hi. I am trying to use a toggle button to clear all cells of color and text, but only the cells that contain the word “event”. And then I’d like to use the same toggle button to add the color and the word “event” back onto the spreadsheet. Can this be done?
Reply
0
0
Nol Kirchyunger
Nol Kirchyunger
This comment was minimized by the moderator on the site
Hallo,

Hoe kan ik cellen in een range met een formule intact houden met bovenstaand script?

Mvg,

Nol
Reply
0
0
skyyang
skyyang     Nol Kirchyunger
This comment was minimized by the moderator on the site
Hello, Nol,
Do you mean to keep the formula cells when clearing the cell contents? Could you explain your problem by using English?

Thank you!
Reply
Report
0
0
Nol Kirchyunger
Nol Kirchyunger     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

Thats what I mean indeed

Regards,

Nol
Reply
0
0
skyyang
skyyang     Nol Kirchyunger
This comment was minimized by the moderator on the site
Hi, Nol,,
Maybe the follwoing VBA code can help you:
Sub Clearcells()
Dim xStr As String
Dim xRgs, xRg, xRgA As Range
xStr = "A2:C3,A5:E16,F5:H10"
Set xRgs = Range(xStr)
For Each xRgA In xRgs.Areas
    For Each xRg In xRgA
        If Not xRg.HasFormula Then
            xRg.Clear
        End If
    Next
Next
End Sub


Please change the cell references to your need, and you should use the comma to separate the data range.
Please have a try, hope it can help you!
Reply
Report
0
0
Nol Kirchyunger
Nol Kirchyunger     skyyang
This comment was minimized by the moderator on the site
Thanks Skyyang, the script works like an charm! Keep up the good work
Reply
0
0
Nat
Nat     Nol Kirchyunger
This comment was minimized by the moderator on the site
Can you help me find a code that would clear the contents of merged cells but keep the formatting as well as formulas?
Thank you
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations