Skip to main content

Conas sonraí colún a shórtáil trí chliceáil ar cheanntásc in Excel?

Ag ceapadh, tá raon sonraí agam, anois, ba mhaith liom na sonraí a shórtáil in ord ardaitheach nó íslitheach trí chliceáil ar aon cheanntásc colúin chun an pictiúr seo a leanas a thaispeáint. Conas a d’fhéadfá an post seo a réiteach in Excel?

sórtáil doc le cliceáil 1

Sórtáil sonraí trí chliceáil ar cheanntásc an cholúin le cód VBA


mboilgeog cheart gorm saighead Sórtáil sonraí trí chliceáil ar cheanntásc an cholúin le cód VBA

De ghnáth, in Excel, is féidir leat an ghné Sórtáil a chur i bhfeidhm chun na sonraí a shórtáil go tapa agus go héasca, ach, chun sonraí a shórtáil trí chliceáil ar chill amháin, is féidir leis an gcód VBA seo a leanas fabhar a thabhairt duit.

1. Cliceáil ar dheis ar an táb bileog ar mhaith leat sonraí a shórtáil trí chliceáil ar chill, agus roghnaigh Féach an cód ón roghchlár comhthéacs, agus san oscailt Microsoft Visual Basic d’iarratais an cód seo a leanas a fhuinneogú, a chóipeáil agus a ghreamú isteach sa Mhodúl bán:

Cód VBA: Sórtáil sonraí trí chliceáil ar cheanntásc cille nó colúin:

Public blnToggle As Boolean
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice
Dim LastColumn As Long, keyColumn As Long, LastRow As Long
Dim SortRange As Range
LastColumn = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
keyColumn = Target.Column
If keyColumn > LastColumn Then Exit Sub
Application.ScreenUpdating = False
Cancel = True
LastRow = Cells(Rows.Count, keyColumn).End(xlUp).Row
Set SortRange = Target.CurrentRegion
blnToggle = Not blnToggle
If blnToggle = True Then
SortRange.Sort _
Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlYes
Else
SortRange.Sort _
Key1:=Cells(2, keyColumn), Order1:=xlDescending, Header:=xlYes
End If
Set SortRange = Nothing
Application.ScreenUpdating = True
End Sub

sórtáil doc le cliceáil 2

2. Agus ansin fuinneog an chóid a shábháil agus a dhúnadh, anois, nuair a chliceálann tú faoi dhó ceanntásc cille nó colúin laistigh den raon sonraí, sórtálfar an colún in ord ardaitheach, má chliceálann tú arís é faoi dhó, sórtálfar an colún ag ísliú ag an am céanna.


Earraí níos gaolmhara:

Conas luach na cille a athrú trí chliceáil ar an gcill?

Conas sonraí a scagadh ach trí ábhar cille a chliceáil in Excel?

Uirlisí Táirgiúlachta Oifige is Fearr

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 ...

cluaisín kte 201905


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 (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hallo,
der Code funktioniert auch gut bei mir. Allerdings würde ich gerne die oberen beiden Zeilen nicht mit sortieren, da diese die Überschriften sind.
Wie muss ich dann diesen Code ändern?

Vielen Dank!!
This comment was minimized by the moderator on the site
Hello friend,
Here is the VBA you need:

Public blnToggle As Boolean
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice
Dim LastColumn As Long, keyColumn As Long, LastRow As Long
Dim SortRange As Range
LastColumn = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
keyColumn = Target.Column
If keyColumn > LastColumn Then Exit Sub
Application.ScreenUpdating = False
Cancel = True
LastRow = Cells(Rows.Count, keyColumn).End(xlUp).Row
On Error Resume Next
Set SortRange = Target.CurrentRegion
Dim i As Long
i = 2
Set SortRange = SortRange.Offset(i, 0)
Set SortRange = SortRange.Resize(SortRange.Rows.Count - i, SortRange.Columns.Count)
blnToggle = Not blnToggle
If blnToggle = True Then
SortRange.Sort _
Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlNo
Else
SortRange.Sort _
Key1:=Cells(2, keyColumn), Order1:=xlDescending, Header:=xlNo
End If
Set SortRange = Nothing
Application.ScreenUpdating = True
End Sub


If you have headers of 3 rows, just change "i =2" to "i =3" in the VBA. Hope it helps. Have a great day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hi Mandy/all,

Is it possible to change your code to only sort when the headers are double clicked instead of any cell?

Thank you very much!
This comment was minimized by the moderator on the site
Hello,
To solve your problem, please apply the below code:
Public blnToggle As Boolean
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice
Dim LastColumn As Long, keyColumn As Long, LastRow As Long
Dim SortRange As Range
Dim xAddress As String
Dim xRgI As Range
xAddress = "A1:E2" 'The headers
Set xRgI = Intersect(Range(xAddress), Target)
If xRgI Is Nothing Then Exit Sub
LastColumn = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
keyColumn = Target.Column
If keyColumn > LastColumn Then Exit Sub
Application.ScreenUpdating = False
Cancel = True
LastRow = Cells(Rows.Count, keyColumn).End(xlUp).Row
On Error Resume Next
Set SortRange = Target.CurrentRegion
Dim i As Long
i = 2
Set SortRange = SortRange.Offset(i, 0)
Set SortRange = SortRange.Resize(SortRange.Rows.Count - i, SortRange.Columns.Count)
blnToggle = Not blnToggle
If blnToggle = True Then
SortRange.Sort _
Key1:=Cells(2, keyColumn), Order1:=xlAscending, Header:=xlNo
Else
SortRange.Sort _
Key1:=Cells(2, keyColumn), Order1:=xlDescending, Header:=xlNo
End If
Set SortRange = Nothing
Application.ScreenUpdating = True
End Sub



Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
This works perfectly! Thank you very much skyyang!
This comment was minimized by the moderator on the site
No can do crackerjack - don't work
This comment was minimized by the moderator on the site
Hi, Rob, The above code works well in my Excel, can you give your problem a screenshot here?
This comment was minimized by the moderator on the site
Doesn't work, nothing happens, know how to create module in vba, did that, saved and nothing when header double clicked. Please fix it.
This comment was minimized by the moderator on the site
Works ok to ascend, double click a 2nd time as stated to descend does nothing
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations