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

[Tash Point O]

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 TY so much

[/IMG]

[/IMG]

 

[Fluff]

If Col B is values, rather than formulae, another option is

Sub FillDownColA()
   Dim Rng As Range
   With Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      For Each Rng In .Areas
         Rng.Offset(, -1).FillDown
      Next Rng
   End With
      
End Sub

 

[offthelip]

I have annotated it to explaoin everything, I hope it helps you to learn how to use VBa effectively

Dim outarr As Variant
' find last row in column B
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
' load all of columm A in to a variant array called cola
cola = Range(Cells(1, 1), Cells(lastrow, 1))
' load all of columm B in to a variant array called colb
colb = Range(Cells(1, 2), Cells(lastrow, 2))
' load Arow1 with the first number to  write down column
Arow1 = cola(1, 1)


' loop through all the rows
For i = 1 To lastrow
 ' if column B is blank don't wirte anything inot column but pick up the next valu'
 ' for Arow1 from the row below
 
 If colb(i, 1) = "" Then
   Arow1 = cola(i + 1, 1)
 Else
 ' colunm B is not blnk so write the number back into column A
   cola(i, 1) = Arow1
 End If
Next i




' Now write the variant array back into the whole of column A
 Range(Cells(1, 1), Cells(lastrow, 1)) = cola

 

[Tash Point O]

I have annotated it to explaoin everything, I hope it helps you to learn how to use VBa effectively

Dim outarr As Variant
' find last row in column B
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
' load all of columm A in to a variant array called cola
cola = Range(Cells(1, 1), Cells(lastrow, 1))
' load all of columm B in to a variant array called colb
colb = Range(Cells(1, 2), Cells(lastrow, 2))
' load Arow1 with the first number to  write down column
Arow1 = cola(1, 1)


' loop through all the rows
For i = 1 To lastrow
 ' if column B is blank don't wirte anything inot column but pick up the next valu'
 ' for Arow1 from the row below
 
 If colb(i, 1) = "" Then
   Arow1 = cola(i + 1, 1)
 Else
 ' colunm B is not blnk so write the number back into column A
   cola(i, 1) = Arow1
 End If
Next i




' Now write the variant array back into the whole of column A
 Range(Cells(1, 1), Cells(lastrow, 1)) = cola

I read that w/as much enthusiasm as I imagine a kid reading the first page of the newest Harry Potter release at midnight (back in the day). Thank you!

 

[Tash Point O]

If Col B is values, rather than formulae, another option is

Sub FillDownColA()
   Dim Rng As Range
   With Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      For Each Rng In .Areas
         Rng.Offset(, -1).FillDown
      Next Rng
   End With
      
End Sub

I will test this out too, thank you!