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

MrPink1986

Active Member
Joined
May 1, 2012
Messages
252
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
No data provided. If you still need help, suggest you try again and make sure that you have sample data provided.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I can see your raw data, but I am not sure what your mocked up solution should look like. Am I missing something here?
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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