以Excel保留样式排序吗? [英] Sorting in Excel keeping style?
问题描述
我得到了一个背景交替的excel文件,以提高可读性.
I got an excel-file with alternating backgrounds for better readibility.
Row 1: White Background
Row 2: Gray Background
Row 3: White Backgrund
[...]
我使用VBA功能对Excel文件的内容进行排序,通过单击按钮引发该事件:
I use a VBA-Function to sort the contents of the Excel-File, the event is thrown by clicking a button:
Sub SortByName()
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("BO6:BO1024"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("D6:D1024"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A6:DD1024")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
排序就像我想要的那样很好,但是Backgroundstyle也随内容一起移动,这破坏了交替样式,如:
The sorting works fine just like I wanted to, but the Backgroundstyle is also moved with the content, which destroys the alternating style like:
Row 1: White (was row 3)
Row 2: White (was row 1)
Row 3: Gray (was row 2)
[...]
是否有一种方法可以在不复制样式的情况下对内容进行排序?
Is there a way to sort the contents WITHOUT the styles being copied?
推荐答案
我承认这是一个hack,但是下面的方法可以正常工作.它一次只对一个单元格进行固定格式"设置-大概您可以更改此格式以对整个行进行修改
I admit this is a hack, the but the below will work. It's doing the "fix formatting" one cell at a time - presumably you can change this to do it for an entire row
Sub sortNoFormat()
Dim r As Range
Dim f() ' a place to keep the formatting
Dim ii As Integer
Dim c
' set up the sort:
Set r = Range("tosort")
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=r, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
' before sorting, copy the format (color) of each cell to array f:
ReDim f(1 To r.Cells.Count)
ii = 1
For Each c In r.Cells
f(ii) = c.Interior.ColorIndex
ii = ii + 1
Next
' Perform the sort:
With ActiveSheet.Sort
.SetRange r
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' apply the old formatting:
ii = 1
For Each c In r.Cells
c.Interior.ColorIndex = f(ii)
ii = ii + 1
Next
End Sub
我相信很容易看到如何创建几个辅助函数-formats = copyFormats(range)
和pasteformats(range, formats)
,这将使代码更具模块化和可读性.这会将我在上面添加的一些行封装在一个简单的包装器中,因此您的原始代码仅需要两行(以及帮助程序模块中的两个函数).
I trust that it is easy to see how you could create a couple of helper functions - formats = copyFormats(range)
and pasteformats(range, formats)
that would make the code more modular and readable. This would encapsulate some of the lines I added above in a simple wrapper so your original code just needs two lines (and the two functions in a helper module).
这篇关于以Excel保留样式排序吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!