Extend columns in a do until loop

[Allan91]

I have written this code which draws data from a sheet to another with a do until loop but it does it only for 1 column (column U, the 21st column). I want to be able to perform this for 5 columns (columns U, V,W,X,Y). How should I change my code?

Sub DrawData()

Dim x As Integer

x = 1

Do While Worksheets("Clean Data_I").Cells(x, 21).Value <> ""
Worksheets("Clean Data_I2").Cells(x, 1).Value = Worksheets("Clean Data_I").Cells(x, 21).Value
x = x + 1
Loop

End Sub

 

[Joe4]

Assuming your want to copy them to columns A - E, try this:

Sub DrawData()

    Dim x As Integer, y As Integer

    x = 1

    For y = 21 To 25
        Do While Worksheets("Clean Data_I").Cells(x, y).Value <> ""
            Worksheets("Clean Data_I2").Cells(x, y - 20).Value = Worksheets("Clean Data_I").Cells(x, y).Value
        Loop
        x = x + 1
    Next y

End Sub

 

[Allan91]

Assuming your want to copy them to columns A - E, try this:

Sub DrawData()

    Dim x As Integer, y As Integer

    x = 1

    For y = 21 To 25
        Do While Worksheets("Clean Data_I").Cells(x, y).Value <> ""
            Worksheets("Clean Data_I2").Cells(x, y - 20).Value = Worksheets("Clean Data_I").Cells(x, y).Value
        Loop
        x = x + 1
    Next y

End Sub

Unfortunately this did not work. When I tried to run this code it crashed excel. I assumed maybe x=x+1 should have been within the loop. But then it just did it for A. Rest was of the columns were empty.

 

[Joe4]

Please post a sample of your data, so we can see what it looks like, and post what you want your expected results to look like. Then we will be able to recreate your scenario on our side, and test things out.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a “Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Allan91]

This is the Clean Data_I sheet I am trying to copy from. I also have an empty sheet called Clean Data_I2 sheet that will be populated throughout A:E columns. I hope this will work.

DrawData.xlsm
	U	V	W	X	Y
1	Items	Month	Income	Number of Items	Number of Customers
2	Mug	January	5600	37	4
3	Plate	January	195	3	1
4	Cup	January	80	2	1
5	Blah	January	45	3	1
6	Deneme	January
7	Bowl	January
8	Mug	February	2790	11	3
9	Plate	February	195	3	1
10	Cup	February	80	2	1
11	Blah	February	45	3	1
12	Deneme	February
13	Bowl	February
14	Mug	March	2790	11	3
15	Plate	March	195	3	1
16	Cup	March	80	2	1
17	Blah	March	45	3	1
18	Deneme	March
19	Bowl	March
20	Mug	April	2790	11	3
21	Plate	April	195	3	1
22	Cup	April	80	2	1
23	Blah	April	45	3	1
24	Deneme	April
25	Bowl	April
26	Mug	May	2790	11	3
27	Plate	May	195	3	1
28	Cup	May	80	2	1
29	Blah	May	45	3	1
30	Deneme	May
31	Bowl	May
32	Mug	June	2790	11	3
33	Plate	June	195	3	1
34	Cup	June	80	2	1
35	Blah	June	45	3	1
36	Deneme	June
37	Bowl	June
38	Mug	July	2790	11	3
39	Plate	July	195	3	1
40	Cup	July	80	2	1
41	Blah	July	45	3	1
42	Deneme	July
43	Bowl	July
44	Mug	August	2790	11	3
45	Plate	August	195	3	1
46	Cup	August	80	2	1
47	Blah	August	45	3	1
48	Deneme	August
49	Bowl	August
50	Mug	September	2790	11	3
51	Plate	September	195	3	1
52	Cup	September	80	2	1
53	Blah	September	45	3	1
54	Deneme	September
55	Bowl	September
56	Mug	October	2790	11	3
57	Plate	October	195	3	1
58	Cup	October	80	2	1
59	Blah	October	45	3	1
60	Deneme	October
61	Bowl	October
62	Mug	November	2790	11	3
63	Plate	November	195	3	1
64	Cup	November	80	2	1
65	Blah	November	45	3	1
66	Deneme	November
67	Bowl	November
68	Mug	December	2790	11	3
69	Plate	December	195	3	1
70	Cup	December	80	2	1
71	Blah	December	45	3	1
72	Deneme	December	45	3	1
73	Bowl	December	230	5	1
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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
Clean Data_I

 

[Fluff]

How about

Sub Allan()
   With Sheets("Clean Data_I")
      With .Range("U1:Y" & .Range("U" & Rows.count).End(xlUp))
         Sheets("Clean Data_I2").Range("A1").Resize(.Rows.count, 5).Value = .Value
      End With
   End With
End Sub

 

[Allan91]

How about

Sub Allan()
   With Sheets("Clean Data_I")
      With .Range("U1:Y" & .Range("U" & Rows.count).End(xlUp))
         Sheets("Clean Data_I2").Range("A1").Resize(.Rows.count, 5).Value = .Value
      End With
   End With
End Sub

Unfortunately this did not work Fluff. It brings back an object defined error. Plus the reason why I was trying the do while loop is in the original data there are formulas in the next blank cells and with the loop excel recognizes them as absolute blank when I use <>"". With the end(xlUP) it brings it all the way down to where the formulas end, which I don't want because I want to use these data in a pivot table. With end(xlUP) the pivot table recognizes the cells with formulas as data and brings back undeletable empty boxes in slicers.

 

[Fluff]

Which line gives the error?

 

[Allan91]

Which line gives the error?

With .Range("U1:Y" & .Range("U" & Rows.Count).End(xlUp))

This one

 

[Fluff]

Oops, it should be

With .Range("U1:Y" & .Range("U" & Rows.Count).End(xlUp).Row)

Also with this code the formula blanks should not create a problem.