David,
In fact I already have a helper column with the "naked" filenumbers 8 25
123 333 ....etc. and I did sort on that column.
The first is the file corresponding to 1066.xls because Windows/Excel "sees"
ore stores 1066 before 123 because it does so in the alphabetical in stead
of the numerical order (I hope I said it right).
Along all rows of
_________________________________ 1066
| 1066
| 1066
| 1066
| 1066
in column I, so in all cells of column I "belonging" to the part that was
originally 8.xls, there is the number 1066. After that comes
_________________________________ 123
| 123
| 123
| 123
| 123
Because of this I have to sort (numerically) and there the problems arise:
the borderlines stay where the are.
I am now trying out what Dave advised in his last answer.
"David McRitchie" schreef in bericht
...
Hi Jack,
Place the path w/o the filename in another column so
you can sort on that. Sorting the numbers is simple
in fact you could just remove the .xls in a helper column,
or use Text to columns separating at the period, and then
use the trimall macro on my join.htm page to reenter so they
become numbers if they didn't automatically become numbers.
But for other similar things you could look at
http://www.mvps.org/dmcritchie/excel...htm#normdigits
and normalize at 5 digits, and at
Extraction of a Group of Digits and Dashes, from postings by Harlan
Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Jack Sons" wrote in message
...
Dave and David,
I understand.
The problem would not arise if in my code
.............
.............
MyNames = Dir("*.xls")
Do while .........
.............
.............
Dir
loop
.............
.............
would call the files in the current directory (with order as seen below)
8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls
in the order of occurrence in stead of
1066.xls
123.xls
1230.xls
1363.xls
2155.xls
2308.xls
25.xls
333.xls
763.xls
8.xls
This last order seems "logical" to Excel - I understand that - but not to
normal humans. I can't change the names of the files nor the sequence of
the
files in the directory which happens to be the numerical sequence that is
"logical" to humans.
Is there a way to call the files (allmost a thousand files) one by one
corresponding to the sequence in their directory?
If so, my problem would be over!
Jack.
"Jack Sons" schreef in bericht
...
Dave and David,
In principle I agree.
But I have to sort in a very large file of which many groups of cells
that
belong together have a thick horizontal or vertical borderline in
common,
like below (the vertical should be an uninterrupted line). As an
exemple
this range could typically be something like A2503:H2558 with the
horizontal line above A2503:H2503 and the vertical line to the right of
D2503:D2558. In the sorting process the whole range A2503:H2503 has to
be
moved to elsewhere (with the "lines") without leaviing a trace because
its
borders will not correspond with the cells that wil take its place.
_________________________________
|
|
|
|
Any suggestions for code?
Jack.
"Jack Sons" schreef in bericht
...
Hi all,
When I sort a column the order of cells will be rearranged, see this
code:
Worksheets("TRY").Columns("A:C").Sort Key1:=Range("C1"),
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:=False,
Orientation:=xlTopToBottom ', _
'DataOption1:=xlSortNormal.
All cell details will go to the new place of the cell, like value,
text
color, italic, bold etc. and even fill color, but not the cell
borders.
I really need code to sort with which also the cell borders will
change
place, I mean they will "stick" to the cell content, so
1
2
3
will result in
3
2
1
Your help will be appreciated.
Jack Sons
The Netherlands