sorry one more. possible to retain the font, font colour, highlight colour? rather than shift only the letter/number, can it shift it as a whole (with font, font colour, highlight colour)?
With the file I attached or with the code copy/pasted to your actual file?
Try changing the "CustomSort" code to
Leave the ArraySort code as it is.
If you set the required format (font colour etc.) for the whole data area, then the macro will not change that and formatting should be correct after macro run.
ehh.
no. At the data sheet, some number had unique font colour & highlighted to represent something, tht y i cant set it as a whole.
That y the previous script will not change anything and only shift the position.
If you set the required format (font colour etc.) for the whole data area, then the macro will not change that and formatting should be correct after macro run.
This work but it cant retain the original colour / highlight.
Some data is highlighted for special remark. I hope it can retain the riginal colour / highlight. only shift the position.
Try changing the "CustomSort" code to
Display MoreCode
- Sub CustomSort()
- Dim x, y(), z(), List, data(), i As Long, ii As Long, iii As Long, ly As Long, lz As Long
- List = Sheets("customlist").Columns(1).SpecialCells(2)
- With ActiveSheet.Cells(1).CurrentRegion.Resize(, 50)
- x = .Value2
- For i = 2 To UBound(x, 1)
- Erase y: ly = 0: lz = 0
- For ii = 2 To UBound(x, 2)
- If x(i, ii) <> "" Then
- If Not IsError(Application.Match(x(i, ii), List, 0)) Then
- ly = ly + 1: ReDim Preserve y(1 To ly)
- y(ly) = Application.Match(x(i, ii), List, 0)
- Else
- lz = lz + 1: ReDim Preserve z(1 To lz): z(lz) = x(i, ii)
- End If
- x(i, ii) = ""
- End If
- Next
- Call ArraySort(y, 1, ly)
- If lz = 0 Then
- ReDim data(1 To ly)
- For iii = 1 To UBound(data)
- x(i, iii + 1) = List(y(iii), 1)
- Next
- Else
- Call ArraySort(z, 1, lz)
- ReDim data(1 To ly)
- For iii = 1 To UBound(data)
- x(i, iii + 1) = List(y(iii), 1)
- Next
- For iii = 1 To lz
- ReDim Preserve data(1 To UBound(data) + 1)
- x(i, UBound(data) + 1) = z(iii)
- Next
- End If
- Next
- .Value2 = x
- End With
- End Sub
Leave the ArraySort code as it is.
What determines the formatting of each value?
Hi,
On the data, some number had unique font colour or highlighted font colour + font colour. Depend.
Keep the original (font colour or highlighted font colour + font colour) but only shift the position.
What determines the formatting of each value?
I sent you a PM
OK, with the custom formatting applied to the custom list try the attached version.
omg, yes yes yes.. Thanks!!!
i try it.. it working great!
OK, with the custom formatting applied to the custom list try the attached version.
You're welcome. Pleased we got there in the end.
Hi,
After input 5000+ data, there is an error.
It say Run-time error '9': Subscript out of range
Debug @ pivot = x((inLow + inHi) \ 2)
You're welcome. Pleased we got there in the end.
Can you attach the actual file with the 5000+ rows of data.