Extend columns in a do until loop

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA 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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Assuming your want to copy them to columns A - E, try this:
VBA Code:
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
 
Upvote 0
Assuming your want to copy them to columns A - E, try this:
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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
UVWXY
1ItemsMonthIncomeNumber of ItemsNumber of Customers
2MugJanuary5600374
3PlateJanuary19531
4CupJanuary8021
5BlahJanuary4531
6DenemeJanuary
7BowlJanuary
8MugFebruary2790113
9PlateFebruary19531
10CupFebruary8021
11BlahFebruary4531
12DenemeFebruary
13BowlFebruary
14MugMarch2790113
15PlateMarch19531
16CupMarch8021
17BlahMarch4531
18DenemeMarch
19BowlMarch
20MugApril2790113
21PlateApril19531
22CupApril8021
23BlahApril4531
24DenemeApril
25BowlApril
26MugMay2790113
27PlateMay19531
28CupMay8021
29BlahMay4531
30DenemeMay
31BowlMay
32MugJune2790113
33PlateJune19531
34CupJune8021
35BlahJune4531
36DenemeJune
37BowlJune
38MugJuly2790113
39PlateJuly19531
40CupJuly8021
41BlahJuly4531
42DenemeJuly
43BowlJuly
44MugAugust2790113
45PlateAugust19531
46CupAugust8021
47BlahAugust4531
48DenemeAugust
49BowlAugust
50MugSeptember2790113
51PlateSeptember19531
52CupSeptember8021
53BlahSeptember4531
54DenemeSeptember
55BowlSeptember
56MugOctober2790113
57PlateOctober19531
58CupOctober8021
59BlahOctober4531
60DenemeOctober
61BowlOctober
62MugNovember2790113
63PlateNovember19531
64CupNovember8021
65BlahNovember4531
66DenemeNovember
67BowlNovember
68MugDecember2790113
69PlateDecember19531
70CupDecember8021
71BlahDecember4531
72DenemeDecember4531
73BowlDecember23051
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
 
Upvote 0
How about
VBA Code:
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
 
Upvote 0
How about
VBA Code:
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.
 
Upvote 0
Oops, it should be
VBA Code:
With .Range("U1:Y" & .Range("U" & Rows.Count).End(xlUp).Row)
Also with this code the formula blanks should not create a problem.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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