Help selecting variable range for cut and paste

[knittelmail]

I am trying to automate/speed up the process of combining information from two worksheets. Sheet A is a report generated by software. Sheet B is filled with formulas that I want to use on the information in Sheet A. As background, I think Sheet A was created by the devil. It has multiple ranges of merged cells, it has loads of wrapped text, and cells that look empty but have space characters in them. The row heights are also set at a standard row height that hides some of the text in the wrapped cells. I can fix all of that, but...

What I am having trouble with is the variable row location of the ranges I want to select. There are several ranges in Sheet A. They don't always start or end in the same place. The number of columns across is always the same, but the number of rows in each row can change. This means I can't do a straight copy and paste. For example:

The range including A1 might have 3 rows or it might have 10. The number of rows in the first range changes the starting location of the next range and so on down the column. Please pretend that column C is a new instance of column A

Sheet A or Sheet B can be modified so that the rows line up, but I have to do it manually each time. Running a module would be a lot easier and faster.
My thinking is that on Sheet A, I can count the number of occupied cells between the empty cells then subtract that number from a set number of rows in Sheet B (say 20) and insert that number of rows to make everything line up for relatively simple cut and paste.
Can anyone point me in the right direction?
Thank you all very much!

 

[OaklandJim]

I can't promise that I can do this but I'll try. If not me then possibly other people who are smarter about Excel.

Pictures are not very helpful.

If the data is not confidential hopefully you could post a link to your workbook. Or you can enter fake-but-realistic data before providing the link. Put the file on Dropbox, Box, 1Drive, Google Drive etc. Use the link icon above the message area.

Or, consider sharing relevant data using Mr Excel's excellent XL2BB addin that enables you to post a portion of a worksheet. See XL2BB - Excel Range to BBCode for details.

You'll need a VBA solution that does what is required. Hopefully that is not an issue.

What is the order of appearance of the data in column B when it is transferred to column A?

 

[OaklandJim]

After reviewing the pictures the repositioning of data is pretty confusing. Definitely provide some realistic starting data and try again to describe the results.

 

[Skyybot]

Are there always spaces between the Item rows?

 

[knittelmail]

Hello OaklandJim and Skyybot,
Thank you for taking a look at my monster. I have been trying various combinations of Do Until/While loops combined with If loops and I just can't get anything to work. I am a total newbie.
Below is a link to a dropbox file that has samples of before, during and after. Let me know if it doesn't work and I will try a different way. This is not the whole thing, just a chunk from the beginning. There are some notes on each sheet about what is what is what and what I was thinking.

Hopefully this makes things clearer. Skyybot - yes there is always an "empty" row between the sections on the original report. Just one. Sometimes the number of rows "in" each section (between the empty rows) will change. The Formulas sheet that I want to paste into has a fixed number of rows in each section.

Sample.xlsm

Shared with Dropbox

www.dropbox.com

Please let me know if I need to try to clarify further.

Thank you again!!

 

[OaklandJim]

Hey Skyybot. Are you going to grab this one or should I take a crack at it?

 

[Skyybot]

@OaklandJim, it's all you.

 

[OaklandJim]

I'll have a detailed look tomorrow. It seems like we'll need some back-and-forth before I understand the objective. I hope that I can do what you need.

One big thing to note...merged cells should not be used unless absolutely necessary -- which is rarely. They are notoriously difficult to work with! Make cells that contain content wider instead of merging.

 

[OaklandJim]

Yeah this is a beast. It is very confusing. And the objective based on the original post is not clear. I do not see anything that resembles the original pictures.

Describe the overall purpose.

What are these three worksheets for: After Cleanup, Formulas 1 and Finished Product?

How do those worksheets relate to each other and to the Original worksheet?

To the extent reasonable describe the process to get from start to finish.

 

[Skyybot]

Yeah this is a beast. It is very confusing. And the objective based on the original post is not clear. I do not see anything that resembles the original pictures.

Describe the overall purpose.

What are these three worksheets for: After Cleanup, Formulas 1 and Finished Product?

How do those worksheets relate to each other and to the Original worksheet?

To the extent reasonable describe the process to get from start to finish.

@OaklandJim Yeah, that's what I thought as well. A sample Workbook without all of the instructions in it would be better, I think.