Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
barbyy din Bucuresti
 | Femeie
23 ani
Bucuresti
cauta Barbat
23 - 80 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Assuming you are using a table (not just filter), you can use the following code to sort your data at the press of a button.
Replace SheetName, TableName and ColumnName with your own.
Sub TableSort()
Dim ws As Worksheet
Set ws = Worksheets("SheetName")
Dim tbl As ListObject
Set tbl = ws.ListObjects("TableName")
Dim sortcolumn As Range
Set sortcolumn = Range("TableName[ColumnName]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
|
Source:
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Filter by criteria:
Sub Filtreaza_Neeficace()
Dim lo As ListObject
Dim iCol1, iCol2 As Long
Set lo = ActiveSheet.ListObjects(1)
lo.AutoFilter.ShowAllData
iCol1 = lo.ListColumns("Finalizat").index '
lo.Range.AutoFilter Field:=iCol1, Criteria1:="da"
iCol2 = lo.ListColumns("Evaluare Eficacitate actiuni (se repeta?)").index
lo.Range.AutoFilter Field:=iCol2, Criteria1:="DA"
End Sub |
Assuming the column names (in this case Evaluare Eficacitate actiuni (se repeta?) and Finalizat) might change, you can add their indexes instead of their names.
Sub Filtreaza_Neeficace()
Dim lo As ListObject
Dim iCol1, iCol2 As Long
Set lo = ActiveSheet.ListObjects(1)
lo.AutoFilter.ShowAllData
iCol1 = lo.ListColumns(19).index '
lo.Range.AutoFilter Field:=iCol1, Criteria1:="da"
iCol2 = lo.ListColumns(20).index
lo.Range.AutoFilter Field:=iCol2, Criteria1:="DA"
End Sub |
However, if someone enters a new column before those in VBA, then the code will have to be changed for the sorting to work correctly.
Modificat de TRaP (acum 5 ani)
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
Below with 9 columns filter:
Sub Filter_Me()
Dim tbl As ListObject
Dim iCol1, iCol2, iCol3, iCol4, iCol5, iCol6, iCol7, iCol8, iCol9 As Long
Set tbl = ActiveSheet.ListObjects("Table1")
tbl.AutoFilter.ShowAllData
iCol1 = tbl.ListColumns("1").Index
tbl.Range.AutoFilter Field:=iCol1, Criteria1:="A"
iCol2 = tbl.ListColumns("2").Index
tbl.Range.AutoFilter Field:=iCol2, Criteria1:="B"
iCol3 = tbl.ListColumns("3").Index
tbl.Range.AutoFilter Field:=iCol3, Criteria1:="C"
iCol4 = tbl.ListColumns("4").Index
tbl.Range.AutoFilter Field:=iCol4, Criteria1:="D"
iCol5 = tbl.ListColumns("5").Index
tbl.Range.AutoFilter Field:=iCol5, Criteria1:="E"
iCol6 = tbl.ListColumns("6").Index
tbl.Range.AutoFilter Field:=iCol6, Criteria1:="F"
iCol7 = tbl.ListColumns("7").Index
tbl.Range.AutoFilter Field:=iCol7, Criteria1:="G"
iCol8 = tbl.ListColumns("8").Index
tbl.Range.AutoFilter Field:=iCol8, Criteria1:="H"
iCol9 = tbl.ListColumns("9").Index
tbl.Range.AutoFilter Field:=iCol9, Criteria1:="I"
End Sub |
_______________________________________
|
|
| pus acum 4 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2186
|
|
9 level sort table below:
Sub Sort_Me()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
Set iCol1 = Range("Table1[1]")
Set iCol2 = Range("Table1[2]")
Set iCol3 = Range("Table1[3]")
Set iCol4 = Range("Table1[4]")
Set iCol5 = Range("Table1[5]")
Set iCol6 = Range("Table1[6]")
Set iCol7 = Range("Table1[7]")
Set iCol8 = Range("Table1[8]")
Set iCol9 = Range("Table1[9]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add key:=iCol1, SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add key:=iCol2, SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add key:=iCol3, SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add key:=iCol4, SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add key:=iCol5, SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add key:=iCol6, SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add key:=iCol7, SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add key:=iCol8, SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add key:=iCol9, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
End Sub |
_______________________________________
|
|
| pus acum 4 ani |
|