sorting, borders going with cell content

[Jack Sons]

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

 

[Dave Peterson]

Instead of trying to keep the existing borders, could you remove the borders and
reapply?

I would think that this would be more straightforward (er, pronounced easier).

 

[David McRitchie]

I agree with Dave Paterson, but I'd really suggest some other choices.

There are eight borders around a cell (inside and outside), as you already
mentioned the cell borders remain in place when sorting, it would be
impossible for Excel to know how borders are the interact with other
borders if it tried to keep them intact. (pretty picture...)
http://www.mvps.org/dmcritchie/excel/sorting.htm

Each combination of formatting attributes counts toward a limit so
you really don't want to mess with borders if you an help it, keep
them all the same color.

Would suggest you use another means to differentiate your cells
such as Bold, font color, Interior color. If you are already using
all of those things you probably have a very active worksheet, and
might want to tone it down. There is also pattern shading but I find that
very hard to read, and having a laptop almost definitely exasperates
any problems with coloring. Don't forget about Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
in fact you can change the borders with C.F.

 

[Jack Sons]

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 D25032558.
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.

 

[Dave Peterson]

I think you'd have to use the same rules to reapply the borders as you did when
you first applied the borders.

So if you add bottom/top borders between cells in adjacent rows that change
values, you could look for changes.

 

[David McRitchie]

You are not describing what would have to be done.
You will have to either start from scratch each time using a macro,
possibly with a helper column also involved, or perhaps some
Conditional Formatting. You cannot make the sort do something
that it does not do. As stated before, if you do a lot of creative
cell border formatting you may cause trouble with

Cell styles in a workbook specification limit of 4,000

and you really don't want to make that happen.
---

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]

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.

 

[David McRitchie]

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/sorting.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

 

[Dave Peterson]

I'm not sure how windows actually stores the file, but I don't think it does it
alphabetical. You may be viewing in alphabetical order via Windows|Explorer,
but you can change that display just by clicking on the title in detail view.
(This doesn't change the order of how the files are saved by the operating
system.)

But if your files are always ###.xls (no leading 0's and always numeric), then
you could go get all the names and do the sort yourself. I used a QSort
procedure that I saved from a newsgroup post (sorry about not remembering the
author/authors.)

Option Explicit
Sub testme01()

Dim myNames() As Long
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim JustName As String

'change to point at the folder to check
myPath = "C:\My Documents\Excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
'keep only the numeric filenames
JustName = Left(myFile, Len(myFile) - 4)
If IsNumeric(JustName) Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = CLng(JustName)
End If
myFile = Dir()

Loop

If fCtr = 0 Then
MsgBox "No Numeric Files Found!"
Exit Sub
End If

Call qSort(myNames)

For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.
MsgBox myPath & myNames(fCtr) & ".xls"
Next fCtr

End Sub

Public Sub qSort(v, Optional n& = True, Optional m& = True)
Dim i&, j&, p, t
If n = True Then n = LBound(v): If m = True Then m = UBound(v)
i = n: j = m: p = v((n + m) \ 2)
While (i <= j)
While (v(i) < p And i < m): i = i + 1: Wend
While (v(j) > p And j > n): j = j - 1: Wend
If (i <= j) Then
t = v(i): v(i) = v(j): v(j) = t
i = i + 1: j = j - 1
End If
Wend
If (n < j) Then qSort v, n, j
If (i < m) Then qSort v, i, m
End Sub

 

[Dave Peterson]

I googled for "Sub qSort" and keepitcool has posted this several times.

Dave Peterson wrote:

procedure that I saved from a newsgroup post (sorry about not remembering the
author/authors.)

<<snipped>>

 

[Jack Sons]

Dave,

I tried to do as you said.
Now I have this piece of code:

Call qSort(myNames)
For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.
'------------------------------------------------------------------------
Set mybook = Workbooks.Open(myNames)

I think after Call qSort(myNames) myNames is an array sorted like

8.xls
25.xls
123.xls
333.xls
763.xls
1066.xls
1230.xls
1363.xls
2155.xls
2308.xls

(is that called numerical in contrast to alphabetical, is alphabetical the
order 1066.xls 123.xls 1230.xls .... 25.xls ..... 8.xls?).

My next line of code should open the file indicated by the first element of
array myNames, so 8.xls. This won't work because - I suppose -
Workbooks.Open does not accept an array.

I think I am close to the solution, so please help me further.

Jack.

 

[Jack Sons]

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.

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/sorting.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

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.

 

[Dave Peterson]

If you did text to columns, make sure you told excel that that field was General
(not text).

(or select an empty cell
edit|copy
select the range of Text "numbers"
edit|paste special|click Add

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.

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/sorting.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

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" <[email protected]> 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
D25032558. 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" <[email protected]> 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

 

[Dave Peterson]

For fCtr = LBound(myNames) To UBound(myNames)
'do what you want here.

'------------------------------------------------------------------------
Set mybook = Workbooks.Open(mypath & myNames(fctr) & ".xls")

That's what I was trying to show with the msgbox:
MsgBox myPath & myNames(fCtr) & ".xls"

You have to rebuild the name--that's why you couldn't use "0000888.xls" as a
file name and still have it work.

 

[Jack Sons]

Dave,

Finally I got it, works like a charm, thanks a lot.
BTW, qSort is a mistery to me.

Jack.

 

[Dave Peterson]

Just remember where you stored a copy--and use it without thinking.

It works for me!

 

[Jack Sons]

All right, I am good at that.

Jack.

Just remember where you stored a copy--and use it without thinking.

It works for me!