VBA - Do a command in cell until adjacent cell is empty

Tash Point O

New Member
Joined
Feb 12, 2018
Messages
47
Hello -

I am trying to figure out how to copy and paste cell (for example) A1's content into cell A2, A3 and on but only if cell B2, B3 etc has content in it, otherwise, stop the copy/pasting. I also need this to be a relative reference. Please help :eek::) TY so much




n5hkxt.jpg
[/IMG]
2ent6yc.jpg
[/IMG]
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
This should do it:
Code:
Sub test()

Dim outarr As Variant
Arow1 = Cells(1, 1)
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
colb = Range(Cells(1, 2), Cells(lastrow, 2))
For i = 1 To lastrow
 If colb(i, 1) = "" Then
  Exit For
 End If
Next i


ReDim outarr(1 To i - 2, 1 To 1)
For j = 1 To i - 2
 outarr(j, 1) = Arow1
Next j
Range(Cells(2, 1), Cells(i - 1, 1)) = outarr


End Sub

note I haven't done it using copy and paste which is very slow, I have used varinat arrays which is a much faster way of doing it.
 
Upvote 0
This should do it:
Code:
Sub test()

Dim outarr As Variant
Arow1 = Cells(1, 1)
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
colb = Range(Cells(1, 2), Cells(lastrow, 2))
For i = 1 To lastrow
 If colb(i, 1) = "" Then
  Exit For
 End If
Next i


ReDim outarr(1 To i - 2, 1 To 1)
For j = 1 To i - 2
 outarr(j, 1) = Arow1
Next j
Range(Cells(2, 1), Cells(i - 1, 1)) = outarr


End Sub

note I haven't done it using copy and paste which is very slow, I have used varinat arrays which is a much faster way of doing it.


Hey! This code works! However it only works for the specific area of cell 1,1. I have several groupings of this same scenario that start at for example A10, A21
 
Upvote 0
How do you plan on identifying where the next grouping starts? Do you want it to done automatically by running down column A to find the next cell with something in it? or are you going to enter the starting cell manually, either by selecting it or entering it in a message box? And what happens if the data in column B continues past the next value. Your inital requirements were very good and quite clear, your new requirements are not so clear.
The code currently just keeps going until it runs out of data in column B regardless of what is in column A. This appears to me to be what you asked for originally.
 
Upvote 0
^


Hello! The next grouping will be identified by the user manually UNLESS there is a way to run down Column A till it hits a number then it refers to column B whether or not to replicate A's (whichever row it is) new number. Currently The macro works wonderfully when the data set starts at A1/B1. But when I manually shift down to the next group, the macro does not run.

Let me know if I am making sense =) TY for your help with this.

qsphg8.jpg
[/IMG]
 
Upvote 0
try this code:
Code:
Dim outarr As Variant
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lastrow, 1))
colb = Range(Cells(1, 2), Cells(lastrow, 2))
Arow1 = cola(1, 1)


For i = 1 To lastrow
 
 If colb(i, 1) = "" Then
   Arow1 = colb(i + 1, 1)
 Else
   cola(i, 1) = Arow1
 End If
Next i




Range(Cells(1, 1), Cells(lastrow, 1)) = cola
 
Upvote 0
^^


ALMOST!! I was trying to change your code slightly to accommodate but to no avail. What your code is doing is after the initial successful repetition of the 4 digit number in ColA (example A1:A5), the code then, for the subsequent groupings takes the first new number from ColB and replicates that in ColA, rather than taking the new number from ColA and replicating it - The 4 digit numbers in colA should be the ones replicated.

So CLOSE, I can taste it!

30tqhhc.jpg
[/IMG]
 
Upvote 0
sorry typo:
Code:
[COLOR=#333333]Dim outarr As Variant[/COLOR]lastrow = Cells(Rows.Count, "B").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lastrow, 1))
colb = Range(Cells(1, 2), Cells(lastrow, 2))
Arow1 = cola(1, 1)


For i = 1 To lastrow
 
 If colb(i, 1) = "" Then
   Arow1 = cola(i + 1, 1)
 Else
   cola(i, 1) = Arow1
 End If
Next i



 [COLOR=#333333]Range(Cells(1, 1), Cells(lastrow, 1)) = cola[/COLOR]
 
Upvote 0
sorry typo:
Code:
[COLOR=#333333]Dim outarr As Variant[/COLOR]lastrow = Cells(Rows.Count, "B").End(xlUp).Row
cola = Range(Cells(1, 1), Cells(lastrow, 1))
colb = Range(Cells(1, 2), Cells(lastrow, 2))
Arow1 = cola(1, 1)


For i = 1 To lastrow
 
 If colb(i, 1) = "" Then
   Arow1 = cola(i + 1, 1)
 Else
   cola(i, 1) = Arow1
 End If
Next i



 [COLOR=#333333]Range(Cells(1, 1), Cells(lastrow, 1)) = cola[/COLOR]


Oh man, this is awesome, :). I understand about 3% of your code but it's working like a charm. Thank you so much for taking the time to help me with this.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

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