Dim Data Set Wrap Rows

[mrmmickle1]

I have a data set that goes through Column BH. The row length varies (usually 5 or 6 rows). My co-worker uses this data to analyze discrepancies in inventory. However when he prints this information out at the current time he is using about 7 pages of paper. I would like to condense this by wrapping the information. Is there any way to set ranges that will go through: A:O(1), P:AD(2), AE:AS(3) and AT:BH(4). After range 1 I would like to insert a row and then paste the contents of range two below it. Then take the contents of range 2 insert a row and paste the contents of range 3, and finally insert a row below range 3 and then paste the contents of range 4. This way if I print in landscape my coworker will be able to see all of the information he needs. Any help with this would be great.

 

[ZAX]

I Hope This Code Will Do The Job.

Sub GatherData()

Dim G1, G2, G3, R1, R2

R1 = Range("p1:ad1").End(xlDown).Row
R2 = Range("ae1:as1").End(xlDown).Row

Set G2 = Range("p1:ad" & Range("p1:ad1").End(xlDown).Row)
Set G3 = Range("ae1:as" & Range("ae1:as1").End(xlDown).Row)
Set G4 = Range("at1:bh" & Range("at1:bh1").End(xlDown).Row)

G2.Select
Selection.Cut
Range("a" & Range("a1:o1").End(xlDown).Row + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = xlCut

G3.Select
Selection.Cut
Range("a" & Range("a1:o1").End(xlDown).Row + 3 + R1).Select
ActiveSheet.Paste
Application.CutCopyMode = xlCut

G4.Select
Selection.Cut
Range("a" & Range("a1:o1").End(xlDown).Row + 7 + R2).Select
ActiveSheet.Paste
Application.CutCopyMode = xlCut

End Sub

ZAX

 

[mrmmickle1]

Zax, It seems that this code was lost on me. I have finally gotten back to working on this project. I understand the logic of the code and I believe that it is quite possible that it will due the trick however I am getting an error on ActiveSheet.Paste is there anyway you can help me resolve this issue? I apologize for not responding sooner. I know that it takes a lot of effort and work. Any help you could offer would be appreciated. Thanks Again, Matt

 

[ZAX]

Zax, It seems that this code was lost on me. I have finally gotten back to working on this project. I understand the logic of the code and I believe that it is quite possible that it will due the trick however I am getting an error on ActiveSheet.Paste is there anyway you can help me resolve this issue? I apologize for not responding sooner. I know that it takes a lot of effort and work. Any help you could offer would be appreciated. Thanks Again, Matt

Try:

Sub GatherData()

Dim G1, G2, G3

Set G2 = Range("P1:AD" & Range("p" & Rows.Count).End(xlUp).Row)
Set G3 = Range("ae1:as" & Range("ae" & Rows.Count).End(xlUp).Row)
Set G4 = Range("at1:bh" & Range("at" & Rows.Count).End(xlUp).Row)

G2.Select
Selection.Cut Range("a" & Range("a1:o1").End(xlDown).Row + 2).Select

G3.Select
Selection.Cut Range("a" & Range("a1:o1").End(xlDown).Row + 3 + R1).Select

G4.Select
Selection.Cut Range("a" & Range("a1:o1").End(xlDown).Row + 7 + R2).Select

End Sub

ZAX

 

[mrmmickle1]

Try:

Sub GatherData()

Dim G1, G2, G3

Set G2 = Range("P1:AD" & Range("p" & Rows.Count).End(xlUp).Row)
Set G3 = Range("ae1:as" & Range("ae" & Rows.Count).End(xlUp).Row)
Set G4 = Range("at1:bh" & Range("at" & Rows.Count).End(xlUp).Row)

G2.Select
Selection.Cut Range("a" & Range("a1:o1").End(xlDown).Row + 2).Select

G3.Select
Selection.Cut Range("a" & Range("a1:o1").End(xlDown).Row + 3 + R1).Select

G4.Select
Selection.Cut Range("a" & Range("a1:o1").End(xlDown).Row + 7 + R2).Select

End Sub

ZAX

It seems that I am getting a run-time error 1004 when I execute the code.

I found this info: In Office Excel 2003, when you programmatically set a range value with an array containing a large string, you may receive an error message similar to the following. This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.

The thing is that the strings in the cells are not particularly long. Certainly not over 911 characters.

I appreciate the help
​

-Matt

 

[ZAX]

It seems that I am getting a run-time error 1004 when I execute the code.

I found this info: In Office Excel 2003, when you programmatically set a range value with an array containing a large string, you may receive an error message similar to the following. This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.

The thing is that the strings in the cells are not particularly long. Certainly not over 911 characters.

I appreciate the help
​

-Matt

Hi Matt,
The problem was that there was ".Select" after the range in the second code, Anyway, Hope this works:

Sub GatherData()

Dim R1, R2

R1 = Range("p1:ad1").End(xlDown).Row
R2 = Range("ae1:as1").End(xlDown).Row

Range("P1:AD" & Range("p" & Rows.Count).End(xlUp).Row).Cut Range("a" & Range("a1:o1").End(xlDown).Row + 2)

Range("ae1:as" & Range("ae" & Rows.Count).End(xlUp).Row).Cut Range("a" & Range("a1:o1").End(xlDown).Row + 3 + R1)

Range("at1:bh" & Range("at" & Rows.Count).End(xlUp).Row).Cut Range("a" & Range("a1:o1").End(xlDown).Row + 7 + R2)

End Sub

ZAX

 

[mrmmickle1]

Thank You. I got it up and running! I appreciate the help.

 

[ZAX]

Thank You. I got it up and running! I appreciate the help.

Ur welcome!