Loop through column to cut and paste data

Hello everyone,

I would like your support on the below please. As you will see I'm not an experienced VBA user, rather I would say I'm beginner.

So I was wondering if you can help me with a simple issue for the experienced users. I have a spreadsheet with +150k rows from which on column L I want the code to go through each cell and when it finds the value "BPXN" then to cut the line(s) underneath it and paste them to the right in specific columns.

For example :
If in cell L350 finds the value "BPXN" and the value in cell L351 is different from "BPXN" then to cut the values from L351 to W351 and paste them to X350 to AI350. After that if the value in cell L352 is also different from "BPXN" to cut the values from L352 to W352 and paste them to AJ350 to AU350 and so on. In other words for each row that it finds under the "BPXN" until it finds again the next "BPXN" to cut them and paste them 12 columns after the previous paste.
I have attached an example file in which I think I'm explaining what exactly I'm looking for.

The code that I have below is from what I recorded while doing this and from what I could find in the web. So don't expect something like quality because as I said I'm quite a beginner.
I solved my problem with 2 different recordings of macro but it was good for 100 rows now that I have 150.000 rows to deal with I'm making mistakes and I have to do everything from the beginning some times.

Please Login or Register  to view this content.

I really hope that someone will be able to help me. Thank you in advance for using your time to help me.

Hello Markdk,

It will be easy for anyone in here to help you achieve what you are asking for. But, it seems like you have thought about this the wrong way, especially when it comes to saving the cell content 12 columns to the right...

Can you try to describe what you are trying to achieve in the end and I can help to propose a better solution and include the VBA solution for that.

Hi,

so it should be better, faster, more efficient whatever, to start from the csv text file rather than after importing it …

Hello Marc L,

I wish it was that easy. You see the place where I'm downloading the csv has the restriction of 50 entries at a time which then returns in csv with 200 to 250 rows according to the data. Then I have to copy and paste the data from the csv in my file and then do what I'm trying to do here.

Hi Markdk,

What I meant with my question was more, what is the end goal of this operation? To pasting things into columns, not even knowing how many columns it will be is not a good idea. How are you going to read the data after, what are you going to do with it. It seems structurally not productive the way you want it.

But trusting that you know best regarding this so providing a solution (might be many) as requested.

I have tested this with your example and it is working fine. In your example the data started on row 15 (lngRow) and on Col L (intDataCol), these can be changed to fit your need.

Good luck!

Please Login or Register  to view this content.

MrChulo,

Thank you very much for your time and your solution. It worked perfectly.
Really I would be strangling for days to get it through.

Importing that amount of data when each file just have that few rows does also seem like something to do with Excel, then you could as Marc L says, do this conversion on import.

Hope you are getting paid for so much manual work!

This is something that I don't understand as well.
You see the developers of the company that I'm working on doesn't seem to have thought it through.
What I'm doing now is to try and connect an encrypted ID from 1 data base through the csv that I'm downloading with a public ID from another data base so then I can go to a 3rd data base and get the actual data that I need.
The 1st data base has the restriction of 50 entries at a time the 2nd data base has a restriction of 200 entries at a time and the 3rd one doesn't have any limit.
There used to be a way with macros through Internet Explorer but due to confidential data they stopped it. I'm pretty sure though that still this can be done with the correct credentials and security but due to the confidentiality we cannot reach out for help to people like you for example that you might know how to do it.
And to answer to your question, no unfortunately I'm not getting paid for all this manual work. Again thank you for your time, I really appreciate it.

Originally Posted by Markdk

I wish it was that easy. You see the place where I'm downloading the csv has the restriction of 50 entries at a time which then returns in csv with 200 to 250 rows according to the data. Then I have to copy and paste the data from the csv in my file and then do what I'm trying to do here.

Just attach a couple of source csv text file and the expected result workbook accordingly …