Filling down a column with a range/length relative to another column-CodePudding

I'm trying to fill a column down with a specific value. I stupidly used the xlDown function, which crashed Excel. I want the cells of column B to be filled only as long as the cells in column A aren't empty.

My idea at the moment is to declare a variable and initialize it with the value of the length of any other column, then set that variable as the end of the range, but I think using a loop would be a better alternative?

Sub fillColumn()

Dim myrange as Range
Set myrange = Columns(1)

For Each cell In myrange.Cells

Do While cell <> vbNullString
Cell.Offset(0,1).Value = "Yes"

Next
Loop

End Sub()

I can't figure out how to handle do-while loops in conjunction with for loops, though, so I would appreciate any tips.

CodePudding user response：

Please, use the next way, not needing any iteration:

Sub fillBforNotEmptyA()
   Dim sh As Worksheet, lastR As Long, rngA As Range
   
   Set sh = ActiveSheet 'use here the sheet you need
   lastR = sh.Range("A" & sh.rows.count).End(xlUp).row 'last row in A:A
   Set rngA = sh.Range("A2:A" & lastR)                 'the reference range
   
   sh.Range("B2:B" & lastR).Value2 = Evaluate("IF(" & _
                    rngA.address & "<>"""",""Yes"","""")")
End Sub

CodePudding user response：

How about:

find first row within specific column

find last row within specific column

for i = firstrow to lastrow
  if cells(i, yourColumn).value2 <> vbnullstring then
     cells(i, yourColumn 1).value2="yes"
  end if
next i