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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,932
Office Version
  1. 2019
Platform
  1. Windows
Please use XL2BB and provide a small sample of what your data looks like. Then provide a mocked up sample of what you want the solution to look like based upon your sample data.
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
Please use XL2BB and provide a small sample of what your data looks like. Then provide a mocked up sample of what you want the solution to look like based upon your sample data.
I seemed to be able to sort it out
MrExcel.xlsm
D
7
Sheet1

This is a sample of the data I am working with
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
MrExcel.xlsm
A
1test1
Sheet1

This is my expected out put from the above starting point
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
I completed this as a once off for half of the data which took 2 hours - the process was to go into each column when column a is populated and cut and paste the date upwards in the column.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,932
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

No data provided. If you still need help, suggest you try again and make sure that you have sample data provided.
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
Hi,
First time using the add in so apologies. This is my end state.
MrExcel.xlsm
ABCDEFGHIJK
1test1test2test3test4test5test6test7test8test9test10test11
2DataDataDataDataDataDataDataDataDataData
3DataDataDataData
4DataDataData
5DataData
6DataData
7DataData
8DataData
9DataData
10DataData
11DataData
12DataData
13DataData
14DataData
15DataData
16DataData
17DataData
18DataData
19DataData
20Data
21Data
22Data
23Data
24Data
25Data
26Data
27Data
28Data
29Data
30Data
31Data
32Data
33Data
34Data
35Data
36Data
37Data
38Data
39Data
40Data
41Data
42Data
43Data Set#2DataDataDataDataDataDataData
44DataDataData
45DataData
46DataData
47Data
48Data
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
Sheet1
 

MrPink1986

Active Member
Joined
May 1, 2012
Messages
250
No data provided. If you still need help, suggest you try again and make sure that you have sample data provided.
Apologies on that - I have now attached my starting point - this is my data with the gaps.
MrExcel.xlsm
ABCDEFGHIJK
1test1test2test3test4test5test6test7test8test9test10test11
2DataDataDataDataDataDataDataDataDataData
3
4
5
6
7
8
9
10
11DataDataDataData
12DataDataData
13DataData
14DataData
15DataData
16DataData
17
18
19
20DataData
21DataData
22DataData
23DataData
24DataData
25DataData
26DataData
27DataData
28DataData
29DataData
30DataData
31Data
32Data
33
34
35
36
37Data
38Data
39Data
40Data
41Data
42Data
43Data
44Data
45Data
46Data
47
48
49
50
51Data
52Data
53Data
54Data
55Data
56Data
57Data
58Data
59Data
60Data
61Data
62Data Set#2DataDataDataDataDataDataData
63DataDataData
64
65
66DataData
67DataData
68Data
69
70Data
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
Sheet1
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,932
Office Version
  1. 2019
Platform
  1. Windows
I can see your raw data, but I am not sure what your mocked up solution should look like. Am I missing something here?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,085
Messages
5,545,873
Members
410,711
Latest member
Josh324
Top