Trying to UNION same cell and blank cells. Is this possible?

[pawest]

Hello,

I'm trying to select the same cell and a blank cell multiple with a UNION(). Is this possible?

Here's a summary of my code:

Dim c
Dim Rng As Range
Dim blankC As Range

Set blankC = Range("XFD1048576")

Set Rng = Union(Cells(c.Row, 1), Cells(c.Row, 4), Cells(c.Row, 2), _
                        Cells(c.Row, 3), Cells(c.Row, 5), blankC, blankC, Cells(c.Row, 11), Cells(c.Row, 6), _
                        Cells(c.Row, 8), Cells(c.Row, 7), Cells(c.Row, 13), blankC, blankC, blankC, blankC, _
                        Cells(c.Row, 10), Cells(c.Row, 13), blankC, blankC, blankC, blankC, blankC, blankC, _
                        Cells(c.Row, 14), Cells(c.Row, 15), blankC, blankC, blankC, blankC, blankC, blankC, blankC, _
                        Cells(c.Row, 22))

Ultimately, I'm trying to copy various cells and reorganize them including blank cells to conveniently use UNION for a quick copy and paste as opposed to explicitly defining where to paste everything.

Any suggestions? Thanks!

 

[Rick Rothstein]

I am not sure about anyone else, but I am not following what you are attempting to do here at all. Perhaps you could describe what you have and how you want it to look afterwards in more detail? Example before/after data would be real useful.

 

[JoeMo]

Copy and paste doesn't work for a non-contiguous range selection. If all the cells, including the blanks, are in a contiguous range, then you don't need the Union to copy them.

 

[pawest]

Thanks for the quick responses.

Additional background: I have a "Data" sheet and a "Data Summary" Sheet. I'm trying to copy continuous data from the Data sheet, reorganize it using UNION and blank cells, and copy and paste the reorganized union range to the Data Summary sheet in suitable format.

I understand that copy and paste doesn't work with non-contiguous ranges which is why I tried adding blank cells.

The c stands for cell and blankC stands for blankCell as I'm sure you both have realized, but just wanted to clarify.

Any thoughts?

 

[Rick Rothstein]

Thanks for the quick responses.

Additional background: I have a "Data" sheet and a "Data Summary" Sheet. I'm trying to copy continuous data from the Data sheet, reorganize it using UNION and blank cells, and copy and paste the reorganized union range to the Data Summary sheet in suitable format.

I understand that copy and paste doesn't work with non-contiguous ranges which is why I tried adding blank cells.

Any thoughts?

I think I would still like to see example data, but if I am following what your ultimate goal is, then perhaps the code I posted here in my mini-blog can be of help to you...

Swapping
(Rearranging) Multiple Columns of Data

 

[pawest]

That's exactly what I'm trying to do, but only take certain cells and incorporate blank cells.

I think I could solve my problem quickly if I knew of a way to specify a blank cell variable and use that multiple times in the Union function.

I'll try to simplify an example of what I want the UNION output to look like:

' "Data" sheet:
Col A          Col B          Col C
22             42             5

' "Data Summary" Sheet:
Col A          Col B         Col C          Col D          Col E
5              ""              ""              42            ""

 

[pawest]

Rick,
With the great piece of code that you created and referenced...

<code>Sub RearrangeColumns()   Dim X As Long, LastRow As Long, Letters As Variant, NewLetters As Variant   
Const NewOrder As String = "C,B,E,F,H,AC,K,AF,T,M,S,G,X,I,Z,AA,L,AG,AE,AH,AD,AI,A,D,J,N,O,P,Q,R,U,V,W,Y,AB,AJ"   
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row   Letters = Split(NewOrder, ",")   
ReDim NewLetters(1 To UBound(Letters) + 1)   
For X = 0 To UBound(Letters)     
NewLetters(X + 1) = Columns(Letters(X)).Column   
Next   
Range("A1").Resize(LastRow, UBound(Letters)) = Application.Index(Cells, Evaluate("ROW(1:" & LastRow & ")"), NewLetters) 
End Sub</code>

Is there a creative way to copy and move around the values as opposed to moving around the actual columns?

 

[Rick Rothstein]

Rick,

Is there a creative way to copy and move around the values as opposed to moving around the actual columns?

I am not sure I understand what problem you are trying to address. Do you mean you only want to move one row of data around instead of all the data? Or do you mean you have formulas in one or more of the cells and the rearrangement you want the cell values to replace the formulas? Or did you mean something else?

 

[pawest]

Rick, thanks for your response.

I'm trying to move data. Not all of the data, however; just some of the data that meets certain criteria. When I move it to the data summary sheet, I want it to be reorganized differently (which is why I rearranged the data and tried to add blank cells... I did that to meet the formatting of the new sheet. Since I'm not trying to move all the data and just some of the data, I do not want to rearrange the columns.

Also, there are no formulas involved.

I'm experimenting with arrays, but I could really create less code and more efficiency in the future if I can figure out how to use Union or tweak your previous solution.

Thanks.

 

[Rick Rothstein]

Rick, thanks for your response.

I'm trying to move data. Not all of the data, however; just some of the data that meets certain criteria. When I move it to the data summary sheet, I want it to be reorganized differently (which is why I rearranged the data and tried to add blank cells... I did that to meet the formatting of the new sheet. Since I'm not trying to move all the data and just some of the data, I do not want to rearrange the columns.

Are you moving more than one row of data? If so, is each row governed by the same criteria for selection and is the ordering the same for each row... in other words, are you processing the rows one at a time because each row might have different columns processed or are you doing all the rows together as a group?

Can you post say, 3 lines of real data for us? Also, can you tell us what the criteria mentioned above is? And can you tell us the order for the reordered columns

If you give us all of the above, we might be able to develop a solution for you that actually applies to your existing setup.