Cut Cell Data But Don't Remove Fill Color?

[miketurn]

Hello Everyone,

I am currently using Excel 2000 and I was wondering if someone could help me out, I would imagine this has been fixed in newer versions but I am not sure.

Say you have Column A the first three rows have a fill color of RED
The rest of Column A has a fill color of YELLOW
Rows 10, 11 and 12 have text in them (with YELLOW filled background)
If you select 10, 11 and 12 and go to cut
Then you paste them in Column A in Rows 1, 2 and 3

Rows 1, 2 and 3 will have the data and the YELLOW fill pasted in them (which is great)
but left behind is rows 10, 11 and 12 and they now are no longer YELLOW, they are now WHITE

The fill color gets removed completely, where I wanted them to stay YELLOW as well..


Is there a simple way to prevent this from happening?

Would imagine a macro could fix this, but it seems like it would be overkill for something that I hope has a simpler solution.

If anyone has any advice, please share.
Thank You to anyone who reads this

(Sorry to hear about the attacks on this site, seems odd that so many forums seem to be having problems like this, what would be the point of doing something like that, to an Excel Forum?)

 

[miketurn]

@Michael M
Thank You for your response, but the two macros provided are too literal to my example, meaning that they are created literally for rows 10-12 and move them to A1. That was just an example, but I was looking for something a little more dynamic where this could be applied anywhere.

The procedure for the "CLEAR CONTENTS" macro, would be.....
I will manually...
1.) COPY selected cell or multiple selected cells data
2.) Select a cell of my choosing with my mouse

Here is where the macro would probably come into play....
3.) PASTE that data into this new selected cell location
4.) Some how have Excel know where that PASTED data (that was COPIED from step one) actually came from and then clear those cells contents.


__________________________________________________________________________________________________________



The procedure for the "REMOVE ROWS" macro, would be.....
I will manually...
1.) COPY selected cell or multiple selected cells data
2.) Select a cell of my choosing with my mouse

Here is where the macro would probably come into play....
3.) PASTE that data into this new selected cell location
4.) Some how have Excel know where that PASTED data (from step one) actually came from and then REMOVE those rows.



Does anyone know if Excel / its scripting language has the ability to remember the location (cells) of where COPIED data came from?
Very similar to Undo and Redo when you think about it.
Thank You

 

[Michael M]

OK, try this......I used rows 10-12, simply because that was your example provided !!

Sub MM1cpy()
Set dr = Application.InputBox("Please select the rows to COPY with your Mouse.", Type:=8)
Set rr = Application.InputBox("Please select a row to PASTE to with your Mouse.", Type:=8)
dr.Copy rr
dr.ClearContents
End Sub

Sub MM1del()
Set dr = Application.InputBox("Please select the rows to COPY with your Mouse.", Type:=8)
Set rr = Application.InputBox("Please select a row to PASTE to with your Mouse.", Type:=8)
dr.Copy rr
dr.Delete
End Sub

 

[miketurn]

@Michael M
Thank You for the last two tools you provided, they do work but for the amount of times that I would use them (which would be a lot) there may be too many steps in the process or maybe it just feels a little too unnatural to me if I had to use it many times.

I am however really impressed with what you came up with, and it does use something that I have never seen before (which might be the cause of the "unnatural" feeling I mentioned) where the dialog boxes pop up requesting a selection of cells, I didn't realize this could be done, very interesting, thank you for showing me this.

The current method I am using is manually copying and pasting and then having to go back and delete the original source, which I dislike, but have done it so many times that it feels more natural in comparison. The stuff I mention in my post #11 of this thread use a manual aspect in steps 1, 2 and 3 the macro that I would be looking for is what the step 4 states, which is questionable if that can even be done in Excel at all.

Again thank you for what you provided, this is just a tough one because of what is needed from Excel because it would have to remember where it got the information from.
Like I mention above, the undo and redo use a process very similar but I don't know if that information can be used in a macro?

Anyways, if you or anyone else has any ideas please share. I am sure I am not the only one who finds the current method provided by Excel to be a little bit of a hassle, so I would think this tool could be helpful to others as well (at least I hope so).

 

[Michael M]

Well we can reduce it by one Inputbox !!...As long as the are to be copied has been selected first, which to me might cause issues !!!

Sub MM1cpy()
Set dr = Selection
Set rr = Application.InputBox("Please select a row to PASTE to with your Mouse.", Type:=8)
dr.Copy rr
dr.ClearContents
End Sub

Sub MM1del()
Set dr = Selection
Set rr = Application.InputBox("Please select a row to PASTE to with your Mouse.", Type:=8)
dr.Copy rr
dr.Delete
End Sub

 

[miketurn]

@Michael M,
Thank You for providing an update of your macro, and for sticking with me on trying to find a solution for this. I am a little surprised that no on else would find something like this helpful, I figured more would dislike having to copy the data to move it with its background color and then have to go back and delete or remove the cells manually every time this action had to be done. I do this a great deal, but maybe others don't?

Anyways, the newest tool you have provided is very close, but I was hoping to be able to eliminate the input box, but unfortunately I have a feeling that that is what is driving the macro itself.

I don't think Excel / VB has this ability, but do you or anyone else know if the input box from the macro in response #14 can be eliminated and replaced with different coding or even be hidden?
Right now the input box pops up asking the user to select where the data should be pasted and then you have to click "ok" and it performs the command, is there some kind of coding that can allow you to remove this input box and be able to just "paste" in the next cell selected?
Or be able to have the input box be hidden and when it receives the location of the selected cell that you want to paste in, automatically hit "ok" in this hidden input box?

Hopefully, what I am saying is making some kind of sense, and is hopefully possible.

As I mentioned thank you very much for your help, your efforts are greatly appreciated.

 

[Michael M]

probably others do find this helpful, but realise the limitations and accept the need for the InputBox...
The problem, I believe, is that you need to macro to "break", while you select the paste location !!
Once you do this you lose the copy location.....hence the InputBox, which is our psuedo break in the code !!

 

[miketurn]

@Michael M,
Ahhh, that is what I was afraid of, I was hoping that there was a way to keep the macro going somehow, just long enough to get that cell location to paste to without the input box.
You know like a "paste in the next selected cell" kind of a code
That is also where my wishful thinking came into play with a hope for some kind of magical "hidden" input box.
Thanks Again

If anyone has secret types of coding to share, to accomplish this please share.
Thank You

 

[Michael M]

Hmmm.....if only...!!!!

 

[Michael M]

Mike
I suppose the obvious question is, why do you need to do it this way ??
It seems by your previous comments, that you are rearranging data !
This may be able to be done by other methods rather than trying to manually substitute for the copy / paste / clear steps

 

[miketurn]

@Michael
Yes, basically for rearranging data that has colored fill backgrounds.
Here is basic general example (not specific to these exact cells).
In this example say each cell has a "yellow" background fill

____________A_____________
1
2 Apple
3 Banana
4
5 Kiwi

Say you need to move Row 5 "Kiwi" to Row 1

If you CUT Row 5 and PASTE it to Row 1 you get an empty white row 5 that you have to either manually fill its background color yellow again or manually remove the row itself.
If you COPY Row 5 and PASTE it to Row 1 you now have to manually go back to row 5 and delete its contents or you have to remove the row itself.

This example is obviously simplified but imagine the data is more complex, the cells are not as close together, maybe you have multiple different color background fills, and this process has to be done many times over and over. All the extra cleaning up that has to be done multiplies and can get a little confusing, on top of that wastes more time to achieve.

I was just hoping there was a simple way to leave the color fill behind and be able to move the data around, but it seems that this is not so easy to achieve.