按字母排序和单元格颜色排序 [英] Sorting Alphabetically and by Cell Color
本文介绍了按字母排序和单元格颜色排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试按字母顺序排列多个列表,并按单元格颜色排序,但在一定范围内(因此不需要太长时间)。基本上,VBA假设选择列B行3-88,按字母排序,然后按颜色排序。然后移动到下一列C3:C88等,直到列NY。
I'm trying to sort multiple lists Alphabetically and by Cell Color but within a certain range (so it doesn't take too long). Basically the VBA is suppose to select for example column B rows 3-88, sort by alphabet, then sort by color. Then move to next column C3:C88, etc, until column NY.
当我尝试它,我得到运行时错误1004:对象_Global的方法范围失败。
When I try it, I get Run-time error 1004: Mathod "Range" of object '_Global' failed.
这是我的VBA:
Sub SortAlphaColor()
' Sorts rows within a list from A-Z
' Run Clean all first to avoid sorting blanks
' Set maximum range to avoid sorting too many rows
Dim rngFirstRow As Range
Dim rng As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveSheet
Set rngFirstRow = ws.Range("B3:NY3")
For Each rng In rngFirstRow
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=rng, Order:=xlAscending
'assuming there are no blank cells..
.SetRange ws.Range(rng, rng.Range("B88").End(xlUp))
'VBA from second module
.SortFields.Add(Range(rng), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(198, 239, 206) <- this line is highlighted in debug
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Header = xlYes
.MatchCase = False
.Apply
End With
Next rng
Application.ScreenUpdating = True
End Sub
推荐答案
对我来说:
Sub SortAlphaColor()
Dim rngFirstRow As Range
Dim rng As Range, rngSort As Range
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveSheet
Set rngFirstRow = ws.Range("B3:NY3")
For Each rng In rngFirstRow.Cells
With ws.Sort
Set rngSort = rng.Resize(86, 1) 'to row 88
.SortFields.Clear
.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add(rng, xlSortOnCellColor, xlAscending, , xlSortNormal). _
SortOnValue.Color = RGB(198, 239, 206)
.SetRange rngSort
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next rng
Application.ScreenUpdating = True
End Sub
这篇关于按字母排序和单元格颜色排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文