I don't see why not... I would think a question about a proposed solution would be considered "fair game" as a follow up.
Rick..
. As you can see below I think I have the most of the well understood. I have modified it slightly to help me see what is going on as well as filling it with green comment Graffiti!
<font face=Calibri><SPAN style="color:#007F00">'______________________________________________________________</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Slightly modified with lots of Comments</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> RickRothsteinsConcatenatingBalls()<br><SPAN style="color:#007F00">'NOTE: Change the ## A1 to the address of the top left cell of your</SPAN><br><SPAN style="color:#007F00">'existing data and change the '### G1 to the address of the top left cell where you want the converted table to go to.</SPAN><br> <SPAN style="color:#00007F">Const</SPAN> ExistingTableAnyCellLocation <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1" <SPAN style="color:#007F00">'##</SPAN><br> <SPAN style="color:#00007F">Const</SPAN> NewTableLHCornerLocation <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "G1" <SPAN style="color:#007F00">'###</SPAN><br> <br> <SPAN style="color:#00007F">Dim</SPAN> SourceTableRange <SPAN style="color:#00007F">As</SPAN> Range <SPAN style="color:#007F00">'Give a name and allow all methods and properties of range object to it</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> SourceTableRange = Range(ExistingTableAnyCellLocation).CurrentRegion <SPAN style="color:#007F00">' Give this a specific Range. CurrentRegiuon Property applied to a cell returns a complete range incorporating that cell and any ranges that it either is in or touches</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> SourceTableRangeTableRowsCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">' For a small Table allow it to hve up to 255 Rows</SPAN><br> <SPAN style="color:#00007F">Let</SPAN> SourceTableRangeTableRowsCount = SourceTableRange.Rows.Count <SPAN style="color:#007F00">' This returns the number of rows in the original table</SPAN><br><br> <SPAN style="color:#00007F">Dim</SPAN> FinalTableFirstColumnRange <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#00007F">Set</SPAN> FinalTableFirstColumnRange = Range(NewTableLHCornerLocation).Resize(SourceTableRangeTableRowsCount) <SPAN style="color:#007F00">' Here the resize Property applied to the Range G1 (or Cell G1 here) returns a range increased by the row number, that is to say a range equal to the first column</SPAN><br> <br> SourceTableRange.Columns(1).Resize(, 2).Copy Destination:=FinalTableFirstColumnRange <SPAN style="color:#007F00">' This is one way of copying the first two columns of the original table to The final table</SPAN><br> FinalTableFirstColumnRange.Columns(2).NumberFormat = "@" <SPAN style="color:#007F00">' This gives a format to the second column in the final Table</SPAN><br> <br> FinalTableFirstColumnRange.Offset(0, 1) = _<br> Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & ")")<br> <br> SourceTableRange.Columns(5).Copy Destination:=FinalTableFirstColumnRange.Offset(, 2) <SPAN style="color:#007F00">'Column 5 of Original table is copied to column 3 of the Final table by setting the destination to 2 colums offset from the first column</SPAN><br> <br> FinalTableFirstColumnRange.Cells(1, 0).Offset(0, 1).Value = "Numbers" <SPAN style="color:#007F00">' The current heading in the second column is finally overwriten with "Numbers". This is done here by putting the value"Numbers" in the cell which is offset by 1 column to the first cell in the Final Table First Column</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'RickRothsteinsConcatenatingBalls()</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">''_______________________________________________________________________________</SPAN></FONT>
. One line I just could not understand despite pulling it apart over a few hours last night. Googling about Row() did not get me far either.
My code:
Code:
FinalTableFirstColumnRange.Offset(0, 1) = _
Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & ")")
Your Code:
Code:
Destination.Offset(, 1) = Evaluate("IF(ROW()," & Source.Columns(2).Address & "&"" - ""&" & _
Source.Columns(3).Address & "&"" - ""&" & Source.Columns(4).Address & ")")
. I think I did get close and in doing so mastered the Evaluate function: I was able to replace your one line code with the following loop and I achieve the same results as with your 1 line code
My 3 line Loop alternative:
<font face=Calibri> <SPAN style="color:#00007F">For</SPAN> j = 2 <SPAN style="color:#00007F">To</SPAN> SourceTableRangeTableRowsCount <SPAN style="color:#00007F">Step</SPAN> 1 <SPAN style="color:#007F00">' For each column of data</SPAN><br> FinalTableFirstColumnRange.Cells(1, 1).Offset(j - 1, 1) = Evaluate("" & SourceTableRange.Cells(j, 2).Address & "" & "&"" - ""&" & "" & SourceTableRange.Cells(j, 3).Address & "" & "&"" - ""&" & "" & SourceTableRange.Cells(j, 4).Address & "") <SPAN style="color:#007F00">' the evaluate fuction can be used in VBA to give the reults from a formula in a normal Excel Spreadsheet. Hier we use it simply to give the results of a formula something of the form =B2 & " - " & C2 & " - " & D2. Just a convenient way of doung the concantenating. Further we here maks the addresses B2 C2 D2 variable in a loop. A very tricky syntax!!!</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> j</FONT>
. Exactly how your code line works is really baffling me. Somehow I cannot see wot the Row() bit is doing, but it seems to be essential to be there in any variations of the code line that I have tried.
. I would really appreciate a voll explanation of this code line. (As I mentioned, although it took a lot of trial and error I think that I mastered Evaluate Syntax)
Thanks.
P.s.1 Here is a file with all my codes and variations of yours that I have tried (Your codes and variations I have placed in the second sheet Module which I have renamed Rick)
FileSnack | Easy file sharing
P.s.2 I may not be able to reply again for a while.. Every time I reply to this forum it uses up one of my 5 allowed attempts to log-in, even though I never log-out in the meantime!?!. I am on my last one here!!. And contrarily to what is says about having to wait for 15 Minutes.. -by me that time seems to be about 2 hours!?.
></o
