VBA clean data

MrPowerQuery

New Member
Joined
Aug 7, 2018
Messages
11
Hi there everyone!

i have to edit the following table.

It looks like this(see following first pic):
123477.jpg


There are different sorts of labels in this table. I order to get best results i need to align under each other.
Like in my next picture:

123478.jpg


After each Sort there should be a blank page/label. An the second or third rule is: For each sort there should be copied 2 more following them.

Actually i tried to record a macro and edit the VBA code to get best results. I also tried directly printing it with the macro but got some problems with the offset method getting in the next row after going right 2 times.

Man iam tired of this...
:mad: Aint there any Excel-Bootcamp i could join?

Iam wondering if somebody can help me with this.


Thanks in Advance

greetz
Ben
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
hi, Ben. For VBA - just start coding. You'll get better at it the more you code.

For the particular question. what defines the boundaries of each range/block?

regards, Fazza
 
Upvote 0
hi Fazza,
will do!

hi, Ben. For VBA - just start coding. You'll get better at it the more you code.

For the particular question. what defines the boundaries of each range/block?

regards, Fazza

Really its just a Range("A1:K21") more precisely 11 cells on X-axis and 21 cells on Y-axis and theres an outter border around it.
In Cell A3 is some kind of a item number. Cant post the original table for data protection reasons.

lg
Ben
 
Upvote 0
hi, Ben

I do not understand 100% & need further explanation.

I understand there are blocks of 21 rows x 11 columns. And three of these blocks across the worksheet.

The objective of these specific instructions are not clear to me "After each Sort there should be a blank page/label. An the second or third rule is: For each sort there should be copied 2 more following them."

I do not know how many of these (21 row blocks) are down the page.
Such as there are 3, or 30, or it needs to worked out by the code.
If the codes needs to work it out, what rules/logic are used?

When the code finishes, how many blocks are down the page?
So if before the code runs there are 3 blocks (each 21 rows x 11 columns) across & 3 blocks down, when the code finishes are there simply the same 9 blocks? Or are extra copies or blanks or something added?

Thank you, Fazza
 
Upvote 0

Forum statistics

Threads
1,223,961
Messages
6,175,651
Members
452,664
Latest member
alpserbetli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top