Hi everyone:

I am trying to swap two cells completely, with each other, including value,
All formats, colors, etc. Well, swapping the values is easy. Does anyone
know, how I can swap All the formats (actual format, color, indent, etc)
that a cell can hold? Thanks for your help.

Bob

Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"), ":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count, C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other, including
value, All formats, colors, etc. Well, swapping the values is easy. Does
anyone know, how I can swap All the formats (actual format, color, indent,
etc) that a cell can hold? Thanks for your help.

Bob

Thanks Rick for your sample code. However, I was wondering if you could
help out more on this. You see, basically, I have two arrays. For example,
array A contains the values of some continuous cells. For example For cells
"C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I do
some work, and manipulate my arrays, and B becomes 2,4,3,5,1. Then I want
to replace my original selected cells that gave us A, with A(B(i)), and that
includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful. Thanks for
all your help.

Bob
"Rick Rothstein" wrote in message
...
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"), ":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count, C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other, including
value, All formats, colors, etc. Well, swapping the values is easy.
Does anyone know, how I can swap All the formats (actual format, color,
indent, etc) that a cell can hold? Thanks for your help.

Bob

We need some clarification on your *new* request (it is only remotely
related to your original question). It looks like A(1) is referencing C3:D3,
A(2) is referencing C4:D4, etc. Given that, what do you mean when you say
A(1)=12, A(2)=17, etc.? I mean, how does a 2-cell range have a single value?
Also, can I assume the A array references can be more than 2 cells wide? If
so, what is the maximum width possible? What is the maximum number of rows
that can be referenced by array A? Can I assume your ultimate request is to
rearrange existing rows of data into a new ordering?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Thanks Rick for your sample code. However, I was wondering if you could
help out more on this. You see, basically, I have two arrays. For
example, array A contains the values of some continuous cells. For
example For cells "C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I do
some work, and manipulate my arrays, and B becomes 2,4,3,5,1. Then I want
to replace my original selected cells that gave us A, with A(B(i)), and
that includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful. Thanks
for all your help.

Bob
"Rick Rothstein" wrote in message
...
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"), ":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count, C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other, including
value, All formats, colors, etc. Well, swapping the values is easy.
Does anyone know, how I can swap All the formats (actual format, color,
indent, etc) that a cell can hold? Thanks for your help.

Bob

Hi Rick:

No, A(1) refers to the value of a single cell in a row (for example D3),
A(2) for D4, etc. However, my selection is more than one column. Let me
show you my selection.
A B C D Index (Array B)
1 6 11
2
3 6 12 1
4 9 17 2
5 1 8 3
6 3 5 4
7 3 10 5
8 0
9 20
My original selection is C3:D7. A is an array of values of a column in my
selection that things need to be calculated based on that column. Once I
make the selection, and calculate my things, I obtain Array B, which is the
order of how things should show. The results should be:
A B C D Index (Array B)
1 6 11
2
3 9 17 2
4 3 5 4
5 1 8 3
6 3 10 5
7 6 12 1
8 0
9 20
I hope this clarifies things a bit.

Bob

"Rick Rothstein" wrote in message
...
We need some clarification on your *new* request (it is only remotely
related to your original question). It looks like A(1) is referencing
C3:D3, A(2) is referencing C4:D4, etc. Given that, what do you mean when
you say A(1)=12, A(2)=17, etc.? I mean, how does a 2-cell range have a
single value? Also, can I assume the A array references can be more than 2
cells wide? If so, what is the maximum width possible? What is the maximum
number of rows that can be referenced by array A? Can I assume your
ultimate request is to rearrange existing rows of data into a new
ordering?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Thanks Rick for your sample code. However, I was wondering if you could
help out more on this. You see, basically, I have two arrays. For
example, array A contains the values of some continuous cells. For
example For cells "C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I do
some work, and manipulate my arrays, and B becomes 2,4,3,5,1. Then I
want to replace my original selected cells that gave us A, with A(B(i)),
and that includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful. Thanks
for all your help.

Bob
"Rick Rothstein" wrote in message
...
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"), ":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count, C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other, including
value, All formats, colors, etc. Well, swapping the values is easy.
Does anyone know, how I can swap All the formats (actual format, color,
indent, etc) that a cell can hold? Thanks for your help.

Bob

That helps some, but you didn't answer all of my questions plus I now have a
couple of additional questions..

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

2. Can there be other data on the rows (either in front of or after the data
that you show being rearrange) that will not move when the designated
columns are rearranged?

3. What is the maximum number of rows that will ever be included for
rearrangement?

4. Do the Index numbers appear in your a column of your spreadsheet or do
they only exist inside your code?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi Rick:

No, A(1) refers to the value of a single cell in a row (for example D3),
A(2) for D4, etc. However, my selection is more than one column. Let me
show you my selection.
A B C D Index (Array B)
1 6 11
2
3 6 12 1
4 9 17 2
5 1 8 3
6 3 5 4
7 3 10 5
8 0
9 20
My original selection is C3:D7. A is an array of values of a column in my
selection that things need to be calculated based on that column. Once I
make the selection, and calculate my things, I obtain Array B, which is
the order of how things should show. The results should be:
A B C D Index (Array B)
1 6 11
2
3 9 17 2
4 3 5 4
5 1 8 3
6 3 10 5
7 6 12 1
8 0
9 20
I hope this clarifies things a bit.

Bob

"Rick Rothstein" wrote in message
...
We need some clarification on your *new* request (it is only remotely
related to your original question). It looks like A(1) is referencing
C3:D3, A(2) is referencing C4:D4, etc. Given that, what do you mean when
you say A(1)=12, A(2)=17, etc.? I mean, how does a 2-cell range have a
single value? Also, can I assume the A array references can be more than
2 cells wide? If so, what is the maximum width possible? What is the
maximum number of rows that can be referenced by array A? Can I assume
your ultimate request is to rearrange existing rows of data into a new
ordering?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Thanks Rick for your sample code. However, I was wondering if you could
help out more on this. You see, basically, I have two arrays. For
example, array A contains the values of some continuous cells. For
example For cells "C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I do
some work, and manipulate my arrays, and B becomes 2,4,3,5,1. Then I
want to replace my original selected cells that gave us A, with A(B(i)),
and that includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful. Thanks
for all your help.

Bob
"Rick Rothstein" wrote in message
...
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"), ":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count,
C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other, including
value, All formats, colors, etc. Well, swapping the values is easy.
Does anyone know, how I can swap All the formats (actual format,
color, indent, etc) that a cell can hold? Thanks for your help.

Bob

Hi Bob,

Without code you would select the cells or cell, hold down the Shift key and
drag the border of the cell to the desired location and release the mouse.
Don't drag the fill handle, any other part of the border.

Then do the same thing with the other cell, back to where the first one was.
--
Thanks,
Shane Devenshire


"Bob" wrote:

Hi everyone:

I am trying to swap two cells completely, with each other, including value,
All formats, colors, etc. Well, swapping the values is easy. Does anyone
know, how I can swap All the formats (actual format, color, indent, etc)
that a cell can hold? Thanks for your help.

Bob

Yes, Rick. I ultimately want to rearrange existing rows of data into a new
ordering, but for my selection only, not the entire row. Array A only
references a single column of data, and array B is its index, if you will.

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

Please note that theoretically, my selection can be the entire 65536x256 of
data, though I doubt it that any user would have that much data. So, the
number of columns vary from 1 to 256.

2. Can there be other data on the rows (either in front of or after the
data that you show being rearrange) that will not move when the designated
columns are rearranged?
Yes. I only want to re-arrange my selection.

3. What is the maximum number of rows that will ever be included for
rearrangement?

I guess, I answered this in question 2 above. The maximum number of rows is
65536.

4. Do the Index numbers appear in your a column of your spreadsheet or do
they only exist inside your code?
Both arrays A and B are only inside my code, as follows:

Dim A() as Variant, B() as Variant, N as long, i as long
N=Selection.rows.count
Redim A(N), B(N)
For i=1 to N
B(i)=i
A(i)=Cells(i, some column in my selection).value ' For example
cells(i,4) for Column D
next i


Now, once the data is calculated, it needs to be dumped into the spreadsheet
fast. I had used a loop before, but it took too long. Someone suggested
that I create a dummy 2D array containing my data and use Selection.Value =
NewDummyArray. Of course before, I did not care about the formats, but now
I am. Basically, think of what I am trying to do, like a sorting operation,
except, I am doing other stuff. I hope this helps. Thanks for your help.
Have a nice Sunday evening.

Bob

"Rick Rothstein" wrote in message
...
That helps some, but you didn't answer all of my questions plus I now have
a couple of additional questions..

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

2. Can there be other data on the rows (either in front of or after the
data that you show being rearrange) that will not move when the designated
columns are rearranged?

3. What is the maximum number of rows that will ever be included for
rearrangement?

4. Do the Index numbers appear in your a column of your spreadsheet or do
they only exist inside your code?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi Rick:

No, A(1) refers to the value of a single cell in a row (for example D3),
A(2) for D4, etc. However, my selection is more than one column. Let me
show you my selection.
A B C D Index (Array B)
1 6 11
2
3 6 12 1
4 9 17 2
5 1 8 3
6 3 5 4
7 3 10 5
8 0
9 20
My original selection is C3:D7. A is an array of values of a column in
my selection that things need to be calculated based on that column.
Once I make the selection, and calculate my things, I obtain Array B,
which is the order of how things should show. The results should be:
A B C D Index (Array B)
1 6 11
2
3 9 17 2
4 3 5 4
5 1 8 3
6 3 10 5
7 6 12 1
8 0
9 20
I hope this clarifies things a bit.

Bob

"Rick Rothstein" wrote in message
...
We need some clarification on your *new* request (it is only remotely
related to your original question). It looks like A(1) is referencing
C3:D3, A(2) is referencing C4:D4, etc. Given that, what do you mean when
you say A(1)=12, A(2)=17, etc.? I mean, how does a 2-cell range have a
single value? Also, can I assume the A array references can be more than
2 cells wide? If so, what is the maximum width possible? What is the
maximum number of rows that can be referenced by array A? Can I assume
your ultimate request is to rearrange existing rows of data into a new
ordering?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Thanks Rick for your sample code. However, I was wondering if you
could help out more on this. You see, basically, I have two arrays.
For example, array A contains the values of some continuous cells. For
example For cells "C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I
do some work, and manipulate my arrays, and B becomes 2,4,3,5,1. Then
I want to replace my original selected cells that gave us A, with
A(B(i)), and that includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful. Thanks
for all your help.

Bob
"Rick Rothstein" wrote in message
...
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"), ":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count,
C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other, including
value, All formats, colors, etc. Well, swapping the values is easy.
Does anyone know, how I can swap All the formats (actual format,
color, indent, etc) that a cell can hold? Thanks for your help.

Bob

LOL... it seems every time you answer my questions, your answers raise more
questions. I'm almost afraid to ask this next question for fear of your
answer.<g This part of your last response is what has got me wondering...

Now, once the data is calculated, it needs to be dumped into the
spreadsheet fast. I had used a loop before, but it took too long.
Someone suggested that I create a dummy 2D array containing my
data and use Selection.Value = NewDummyArray. Of course
before, I did not care about the formats, but now I am.

At what point are you losing your formats... when you update the values in
your selected range (prior to rearranging their rows) or when you physically
move the selected rows around to match the ordering in the B array? If the
latter, I don't think the code will be too bad; however, if the former, then
solution will involve more than just swapping cells or rows around. To boil
my question down, when you update the value in, say, C3, does C3 retain its
formats after the update or are the formats lost right then and there?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Yes, Rick. I ultimately want to rearrange existing rows of data into a
new ordering, but for my selection only, not the entire row. Array A only
references a single column of data, and array B is its index, if you will.

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

Please note that theoretically, my selection can be the entire 65536x256
of data, though I doubt it that any user would have that much data. So,
the number of columns vary from 1 to 256.

2. Can there be other data on the rows (either in front of or after the
data that you show being rearrange) that will not move when the
designated columns are rearranged?
Yes. I only want to re-arrange my selection.

3. What is the maximum number of rows that will ever be included for
rearrangement?

I guess, I answered this in question 2 above. The maximum number of rows
is 65536.

4. Do the Index numbers appear in your a column of your spreadsheet or do
they only exist inside your code?
Both arrays A and B are only inside my code, as follows:

Dim A() as Variant, B() as Variant, N as long, i as long
N=Selection.rows.count
Redim A(N), B(N)
For i=1 to N
B(i)=i
A(i)=Cells(i, some column in my selection).value ' For example
cells(i,4) for Column D
next i


Now, once the data is calculated, it needs to be dumped into the
spreadsheet fast. I had used a loop before, but it took too long.
Someone suggested that I create a dummy 2D array containing my data and
use Selection.Value = NewDummyArray. Of course before, I did not care
about the formats, but now I am. Basically, think of what I am trying to
do, like a sorting operation, except, I am doing other stuff. I hope this
helps. Thanks for your help. Have a nice Sunday evening.

Bob

"Rick Rothstein" wrote in message
...
That helps some, but you didn't answer all of my questions plus I now
have a couple of additional questions..

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

2. Can there be other data on the rows (either in front of or after the
data that you show being rearrange) that will not move when the
designated columns are rearranged?

3. What is the maximum number of rows that will ever be included for
rearrangement?

4. Do the Index numbers appear in your a column of your spreadsheet or do
they only exist inside your code?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi Rick:

No, A(1) refers to the value of a single cell in a row (for example D3),
A(2) for D4, etc. However, my selection is more than one column. Let
me show you my selection.
A B C D Index (Array B)
1 6 11
2
3 6 12 1
4 9 17 2
5 1 8 3
6 3 5 4
7 3 10 5
8 0
9 20
My original selection is C3:D7. A is an array of values of a column in
my selection that things need to be calculated based on that column.
Once I make the selection, and calculate my things, I obtain Array B,
which is the order of how things should show. The results should be:
A B C D Index (Array B)
1 6 11
2
3 9 17 2
4 3 5 4
5 1 8 3
6 3 10 5
7 6 12 1
8 0
9 20
I hope this clarifies things a bit.

Bob

"Rick Rothstein" wrote in message
...
We need some clarification on your *new* request (it is only remotely
related to your original question). It looks like A(1) is referencing
C3:D3, A(2) is referencing C4:D4, etc. Given that, what do you mean
when you say A(1)=12, A(2)=17, etc.? I mean, how does a 2-cell range
have a single value? Also, can I assume the A array references can be
more than 2 cells wide? If so, what is the maximum width possible? What
is the maximum number of rows that can be referenced by array A? Can I
assume your ultimate request is to rearrange existing rows of data into
a new ordering?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Thanks Rick for your sample code. However, I was wondering if you
could help out more on this. You see, basically, I have two arrays.
For example, array A contains the values of some continuous cells.
For example For cells "C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I
do some work, and manipulate my arrays, and B becomes 2,4,3,5,1. Then
I want to replace my original selected cells that gave us A, with
A(B(i)), and that includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful.
Thanks for all your help.

Bob
"Rick Rothstein" wrote in
message ...
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"),
":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count,
C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other, including
value, All formats, colors, etc. Well, swapping the values is easy.
Does anyone know, how I can swap All the formats (actual format,
color, indent, etc) that a cell can hold? Thanks for your help.

Bob

Hi Rick:

I do not think I get your question. I'll try to answer to the best I can
though. I do all my calculations, and obtain array B which has the new
indices. At this point, I want to swap the rows, including the values and
formats. So, for example, if initially, all cells are right aligned except
cell D6 which is left aligned (keep in mind formats could be anything for
any cell), then after the swap, all cells should be right aligned except
cell D4 which is left aligned (the old row 6 is now the new row 4). Give it
a try with sorting in excel. That is what I am trying to achieve, even
though my routine is not to sort. I hope this has clarified things a bit.
Thanks;

Bob


"Rick Rothstein" wrote in message
...
LOL... it seems every time you answer my questions, your answers raise
more questions. I'm almost afraid to ask this next question for fear of
your answer.<g This part of your last response is what has got me
wondering...

Now, once the data is calculated, it needs to be dumped into the
spreadsheet fast. I had used a loop before, but it took too long.
Someone suggested that I create a dummy 2D array containing my
data and use Selection.Value = NewDummyArray. Of course
before, I did not care about the formats, but now I am.

At what point are you losing your formats... when you update the values in
your selected range (prior to rearranging their rows) or when you
physically move the selected rows around to match the ordering in the B
array? If the latter, I don't think the code will be too bad; however, if
the former, then solution will involve more than just swapping cells or
rows around. To boil my question down, when you update the value in, say,
C3, does C3 retain its formats after the update or are the formats lost
right then and there?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Yes, Rick. I ultimately want to rearrange existing rows of data into a
new ordering, but for my selection only, not the entire row. Array A
only references a single column of data, and array B is its index, if you
will.

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

Please note that theoretically, my selection can be the entire 65536x256
of data, though I doubt it that any user would have that much data. So,
the number of columns vary from 1 to 256.

2. Can there be other data on the rows (either in front of or after the
data that you show being rearrange) that will not move when the
designated columns are rearranged?
Yes. I only want to re-arrange my selection.

3. What is the maximum number of rows that will ever be included for
rearrangement?

I guess, I answered this in question 2 above. The maximum number of rows
is 65536.

4. Do the Index numbers appear in your a column of your spreadsheet or
do they only exist inside your code?
Both arrays A and B are only inside my code, as follows:

Dim A() as Variant, B() as Variant, N as long, i as long
N=Selection.rows.count
Redim A(N), B(N)
For i=1 to N
B(i)=i
A(i)=Cells(i, some column in my selection).value ' For example
cells(i,4) for Column D
next i


Now, once the data is calculated, it needs to be dumped into the
spreadsheet fast. I had used a loop before, but it took too long.
Someone suggested that I create a dummy 2D array containing my data and
use Selection.Value = NewDummyArray. Of course before, I did not care
about the formats, but now I am. Basically, think of what I am trying to
do, like a sorting operation, except, I am doing other stuff. I hope
this helps. Thanks for your help. Have a nice Sunday evening.

Bob

"Rick Rothstein" wrote in message
...
That helps some, but you didn't answer all of my questions plus I now
have a couple of additional questions..

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

2. Can there be other data on the rows (either in front of or after the
data that you show being rearrange) that will not move when the
designated columns are rearranged?

3. What is the maximum number of rows that will ever be included for
rearrangement?

4. Do the Index numbers appear in your a column of your spreadsheet or
do they only exist inside your code?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi Rick:

No, A(1) refers to the value of a single cell in a row (for example
D3), A(2) for D4, etc. However, my selection is more than one column.
Let me show you my selection.
A B C D Index (Array B)
1 6 11
2
3 6 12 1
4 9 17 2
5 1 8 3
6 3 5 4
7 3 10 5
8 0
9 20
My original selection is C3:D7. A is an array of values of a column in
my selection that things need to be calculated based on that column.
Once I make the selection, and calculate my things, I obtain Array B,
which is the order of how things should show. The results should be:
A B C D Index (Array B)
1 6 11
2
3 9 17 2
4 3 5 4
5 1 8 3
6 3 10 5
7 6 12 1
8 0
9 20
I hope this clarifies things a bit.

Bob

"Rick Rothstein" wrote in message
...
We need some clarification on your *new* request (it is only remotely
related to your original question). It looks like A(1) is referencing
C3:D3, A(2) is referencing C4:D4, etc. Given that, what do you mean
when you say A(1)=12, A(2)=17, etc.? I mean, how does a 2-cell range
have a single value? Also, can I assume the A array references can be
more than 2 cells wide? If so, what is the maximum width possible?
What is the maximum number of rows that can be referenced by array A?
Can I assume your ultimate request is to rearrange existing rows of
data into a new ordering?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Thanks Rick for your sample code. However, I was wondering if you
could help out more on this. You see, basically, I have two arrays.
For example, array A contains the values of some continuous cells.
For example For cells "C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I
do some work, and manipulate my arrays, and B becomes 2,4,3,5,1.
Then I want to replace my original selected cells that gave us A,
with A(B(i)), and that includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful.
Thanks for all your help.

Bob
"Rick Rothstein" wrote in
message ...
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"),
":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count,
C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other,
including value, All formats, colors, etc. Well, swapping the
values is easy. Does anyone know, how I can swap All the formats
(actual format, color, indent, etc) that a cell can hold? Thanks
for your help.

Bob

I have to do it with code.

"ShaneDevenshire" wrote in
message ...
Hi Bob,

Without code you would select the cells or cell, hold down the Shift key
and
drag the border of the cell to the desired location and release the mouse.
Don't drag the fill handle, any other part of the border.

Then do the same thing with the other cell, back to where the first one
was.
--
Thanks,
Shane Devenshire


"Bob" wrote:

Hi everyone:

I am trying to swap two cells completely, with each other, including
value,
All formats, colors, etc. Well, swapping the values is easy. Does
anyone
know, how I can swap All the formats (actual format, color, indent, etc)
that a cell can hold? Thanks for your help.

Bob

Hi Rick:

What do you think about this:

Disable the update of excel.
Create a new sheet
cut and paste the cells from my existing sheet to this new sheet in the
proper order
Copy the cells from this new sheet to my selection
Delete the new sheet
enable the update of excel

Bob

"Rick Rothstein" wrote:

LOL... it seems every time you answer my questions, your answers raise more
questions. I'm almost afraid to ask this next question for fear of your
answer.<g This part of your last response is what has got me wondering...

Now, once the data is calculated, it needs to be dumped into the
spreadsheet fast. I had used a loop before, but it took too long.
Someone suggested that I create a dummy 2D array containing my
data and use Selection.Value = NewDummyArray. Of course
before, I did not care about the formats, but now I am.

At what point are you losing your formats... when you update the values in
your selected range (prior to rearranging their rows) or when you physically
move the selected rows around to match the ordering in the B array? If the
latter, I don't think the code will be too bad; however, if the former, then
solution will involve more than just swapping cells or rows around. To boil
my question down, when you update the value in, say, C3, does C3 retain its
formats after the update or are the formats lost right then and there?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Yes, Rick. I ultimately want to rearrange existing rows of data into a
new ordering, but for my selection only, not the entire row. Array A only
references a single column of data, and array B is its index, if you will.

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

Please note that theoretically, my selection can be the entire 65536x256
of data, though I doubt it that any user would have that much data. So,
the number of columns vary from 1 to 256.

2. Can there be other data on the rows (either in front of or after the
data that you show being rearrange) that will not move when the
designated columns are rearranged?
Yes. I only want to re-arrange my selection.

3. What is the maximum number of rows that will ever be included for
rearrangement?

I guess, I answered this in question 2 above. The maximum number of rows
is 65536.

4. Do the Index numbers appear in your a column of your spreadsheet or do
they only exist inside your code?
Both arrays A and B are only inside my code, as follows:

Dim A() as Variant, B() as Variant, N as long, i as long
N=Selection.rows.count
Redim A(N), B(N)
For i=1 to N
B(i)=i
A(i)=Cells(i, some column in my selection).value ' For example
cells(i,4) for Column D
next i


Now, once the data is calculated, it needs to be dumped into the
spreadsheet fast. I had used a loop before, but it took too long.
Someone suggested that I create a dummy 2D array containing my data and
use Selection.Value = NewDummyArray. Of course before, I did not care
about the formats, but now I am. Basically, think of what I am trying to
do, like a sorting operation, except, I am doing other stuff. I hope this
helps. Thanks for your help. Have a nice Sunday evening.

Bob

"Rick Rothstein" wrote in message
...
That helps some, but you didn't answer all of my questions plus I now
have a couple of additional questions..

1. Can the rows of data that you are rearranging be more than two cells
wide? If so, what is the maximum number of columns?

2. Can there be other data on the rows (either in front of or after the
data that you show being rearrange) that will not move when the
designated columns are rearranged?

3. What is the maximum number of rows that will ever be included for
rearrangement?

4. Do the Index numbers appear in your a column of your spreadsheet or do
they only exist inside your code?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi Rick:

No, A(1) refers to the value of a single cell in a row (for example D3),
A(2) for D4, etc. However, my selection is more than one column. Let
me show you my selection.
A B C D Index (Array B)
1 6 11
2
3 6 12 1
4 9 17 2
5 1 8 3
6 3 5 4
7 3 10 5
8 0
9 20
My original selection is C3:D7. A is an array of values of a column in
my selection that things need to be calculated based on that column.
Once I make the selection, and calculate my things, I obtain Array B,
which is the order of how things should show. The results should be:
A B C D Index (Array B)
1 6 11
2
3 9 17 2
4 3 5 4
5 1 8 3
6 3 10 5
7 6 12 1
8 0
9 20
I hope this clarifies things a bit.

Bob

"Rick Rothstein" wrote in message
...
We need some clarification on your *new* request (it is only remotely
related to your original question). It looks like A(1) is referencing
C3:D3, A(2) is referencing C4:D4, etc. Given that, what do you mean
when you say A(1)=12, A(2)=17, etc.? I mean, how does a 2-cell range
have a single value? Also, can I assume the A array references can be
more than 2 cells wide? If so, what is the maximum width possible? What
is the maximum number of rows that can be referenced by array A? Can I
assume your ultimate request is to rearrange existing rows of data into
a new ordering?

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Thanks Rick for your sample code. However, I was wondering if you
could help out more on this. You see, basically, I have two arrays.
For example, array A contains the values of some continuous cells.
For example For cells "C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I
do some work, and manipulate my arrays, and B becomes 2,4,3,5,1. Then
I want to replace my original selected cells that gave us A, with
A(B(i)), and that includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful.
Thanks for all your help.

Bob
"Rick Rothstein" wrote in
message ...
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"),
":")(1))
Set C3 = ActiveSheet.Cells(Rows.Count,
C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" wrote in message
...
Hi everyone:

I am trying to swap two cells completely, with each other, including
value, All formats, colors, etc. Well, swapping the values is easy.
Does anyone know, how I can swap All the formats (actual format,
color, indent, etc) that a cell can hold? Thanks for your help.

Bob