 Hello everyone and welcome back to another video. So you join me with the data set and the file that we previously used in our last video where we use VBA to transpose a simple or ever-growing data table so that we didn't have to do it manually. We could literally just run our VBA script and it would paste into the sheet and just to give you a demonstration If I delete this and we run the code you can see that it's gone into sheet number one It's copied our data that we have in this format here And it's gone through to sheet number two and pasted it for us in a transposed manner The purpose of this video is literally to show you how you can add a few lines of code and have this Reorder your columns as you desire So often I've had this experience where I've had maybe a report that has or has not needed to be transposed And then I've been having to manually go through and cut columns and put them into different orders Not necessarily alphabetical order But just because of preference to however that report is needing to be done to be sent to a particular individual So we're going to show you how you can do that just as a simple extension of the code. We've already done So looking at the code window we've got on the left-hand side here You can see at the moment we've obviously stored our data. We've stored it as an array. We've then transposed our data So you might not need this particular step I've got here and it will work perfectly fine All you need to do is store your data in array and then you can Not include this transpose section But I will be leaving it in there for the purpose of the video And this is the part where I simply now go and paste it all into the range But we don't want that what we want to do is we want to reorder these columns Just on the random format just as whatever is desired by our stakeholder So all I'm going to do is just comment out this one at the moment Just put a net in front of it just so that line might execute add some couple of rows And now we're going to show you how we can reorder Our data basically by simply just selecting them out the columns that we want in a particular order that we want them All we need to do is if you go w or go with Ws2 and We go in with statement down here and the reason I'm doing it within a with statement is as you'll see it Just say it's having to type out ws2 multiple times So what we're going to do as it looks very similar in this first part here. We're simply going to go dot range Open brackets and we're firstly going to populate a column a so we want to go a one To a so we only want this data to go into column a and then we're also going to go into an and and what We'll need to do is go into last column So the reason I'm using last column here So we're basically saying in our range We want our range to be in column a and we want it to go down to row last column And if you remember in the last video, we're using last column because before we transposed our data last column Indicated the last date of data that we had available and because it's been transposed obviously that's now going down into rows So hence that's what we're using last column So not to get confused in there with last column and obviously your normal understanding of need and put a row reference here Once we've done that we can go close brackets dot value equals application Dot index Open my array. Oh So my array comma comma one close brackets Done so what I'll just quickly do is I'm just going to populate the rest of these just so we've got all of our columns here So we're currently got nine and then we can obviously continue on and I'll show you what we're doing Okay, so I've been through and populated all of that for our existing columns So if I'm just to remove this row here And we can come down to here You can now see that for columns a through to I so obviously our columns a through to I we have here that I've now Obviously put a reference to each column in terms of pasting the data So you can see this range here value is going to equal obviously the output and at the moment in my array Obviously, we've got these nine different fields to call them that for just for easiest and to Index or reference each one of those columns of data All we need to do is use this simple application index of my array And we want to look at the reference of one and so on through all these files So if we just delete our data here and let's rerun this You can see at the moment It looks exactly the same as it did previously when we had that simple line of code Well, just now actually delete it out here just for not to get confused So after so that gives us exactly the same look and feel if however We didn't want it to be in this order and like the purpose of this video And let's say we wanted city three then two then one All we simply need to do here is go down into our data into column g and just say actually We don't want the seventh Index or column within our data set we want this to be the ninth and we want the seventh to be shown down the bottom here And let's also say that we don't want Edinburgh to be in the fourth position We want it Edinburgh to replace Birmingham. So all we need to do is go to Edinburgh What's the fourth one here and we know Birmingham is here and simply change that to the four and that to a two So if we now Delete our data here or actually what we could do is we could copy this and just paste it on this row here And I was clear these this data up here just so you can remember where our data was now when I run this code You can see how it's all been changed for us So we've now got obviously Edinburgh in position number one here rather than Birmingham And obviously Birmingham is now showing in here rather than Edinburgh and also city three to one order rather than one Two three So I think that's a bit of a short video really because we've done a lot of the content in the previous video If you haven't seen that previous video go check it out now Hopefully there'll be a link on the screen somewhere But that would be a bit more context on how all this code on the left here What it means and how we used it to transpose our original data And then once you're happy with that you all you need to do is use these additional fuel lines of code Which give you the flexibility to then dynamic or not dynamically but to reorder your data as required It's worth mentioning though. We in our first video. We touched on how this was very dynamic So regardless of if new data was added or like as into rows or columns It would pick up our new data The only thing to remember when it comes to using this next bit of code is obviously it's fixed So if you are wanting to have more More data like add like say I can't have a move on to we've got nine rows of data If you wanted to add like a tenth or further Obviously, you would have a limitation. You'd need to make sure you are then still referencing that in here I think I've got the moment So if you're looking at this and transpose data if your number of cities wasn't going to increase any further that was fixed But you're adding more dates that would work fine It's only because these are referring to obviously as it looks here our initial rows So, yeah If you're going to add additional rows and then transpose the data and then use this technique technique to reorder It will be fine And you can continue adding as much date or new dates and dates information as you require But if you want to add new cities, then obviously you'll need to make sure that you're updating it in this with statement Else they won't be included in your data But I said I hope you enjoyed that video Please do give it a like if it's give you an answer to your question or if just showing you something new I'm sure you will find this definitely useful at some point in your Excel career Lastly, you haven't already please do subscribe to the channel and hit that Bell notification button So you are notified of all of our future videos and lastly Thank you very much again for watching and we'll see you in the next video