Macro to move date in columns to ensure there are no blank rows

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
Hi there,

I have a excel sheet which is almost 11k lines and 11 columns big.
Within the columns data is populated sporadically in the rows and I want to be able to bring this data into a defined structure.
The defined structure is that Row 2 will be completed across all columns where there is data in the columns and so on until the next piece of data appears in column a.
Column A will only have one entry per data set.
So A2 has data and A45 has data - between B2 and K44 I want all the data to aligned in the column with no gaps
Row 1 is header data.

Any ideas of this can be done with a macro?
I am leaning towards doing this via filters and uploads - if I record this it wont work as there are lots of variables in the columns with the data.

Any help is muchly appreciated.
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
I can see your raw data, but I am not sure what your mocked up solution should look like. Am I missing something here?
So the difference essentially is that in the raw data there a gaps in the rows between A2-K61 - essentially if there is data in column B4 only I need this to be in B2. All data in the columns between the data in column A needs to have no blank rows as represented in the second dump.

In cell G11:G:16 this data should be moved up to G3 and then the data G20:G30 underneath that.
Each column will have a variable of data that needs to be cut and pasted to the previous blank row in the column
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,932
Office Version
  1. 2019
Platform
  1. Windows
I am sorry to be a pest, but I see no difference in the two spreadsheets that you have presented. They appear to have the same data presented in the same arranged format. You will need to provide a better explanation and example as what you are saying makes no sense to me given the examples presented.
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
MrExcel.xlsm
ADGHIJK
1test1test4test7test8test9test10test11
2DataDataDataDataData
3DataData
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23Data
24Data
25Data
26Data
27DataData
28DataData
29Data
30Data
31Data
32Data
33Data
34Data
35Data
36Data
37Data
38DataData
39Data
40Data
41Data
42Data
43Data Set#2DataDataDataData
44DataDataData
45Data
46
47
48
49
50
51Data
52
53Data
54Data
55Data
56Data
57Data
58
59
60
61
62
63
64
Sheet1


No problem I appreciate your patience with me and hopefully after this we are clear - in this example I have my data - there are essential two data sets here. A2:K42. In these data - each column has data and there are some gaps within it. I want all the data in the columns to sit in to the next free row above and afterwards there should be no gaps between row 2 and the data in the columns. So if we look at column I there is data in I38:I42 - I want this data cut and pasted above in to I2, the next available blank row. This should be repeated for all columns.
The next data set is from A43:K57 - the data set is identified from the data in column A - from A2 to the next value is the data set and likewise in column A. IN my actual data there is hundreds of data sets in column A, here I only have included 2 examples.
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
Here is the data and what the expected result looks like with the data presented in the way I outlined above.
MrExcel.xlsm
ADGHIJK
1test1test4test7test8test9test10test11
2DataDataDataDataDataData
3DataDataDataData
4DataDataData
5DataData
6DataData
7Data
8Data
9Data
10Data
11Data
12Data
13Data
14Data
15Data
16Data
17Data
18Data
19Data
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43Data Set#2DataDataDataData
44DataDataData
45DataDataData
46DataData
47Data
48Data
49
50
51
52
53
54
55
56
57
58
59
60
After
 

Watch MrExcel Video

Forum statistics

Threads
1,114,083
Messages
5,545,857
Members
410,711
Latest member
Josh324
Top