Use column A to define "Range"
Posted on 2014-12-26
An Expert provided me with code that worked fine for the original use but when I tried to adapt it for another problem, I ran into a bit of trouble. The below looks at every row from 1-65536 and where column L has a blank, it writes the word "Blank" into column R and then copies whatever value is in Column K to Column L.
The problem with this is it still writes the word "Blank" after the end of the data-set, (because column L is obviously blank in every row after the end of the data-set.)
What I need is to define the range by looking at column A (which will never have any blanks), and then proceed to execute the rest of the code. I've tried several modifications of the "For Myrow =..." line but I haven't found the solution yet.
Dim Myrow As Long
For Myrow = 1 To 65536 'you can change the 65536 and decrease / increase for any row quantities you want / need
If UCase(Range("L" & Myrow).Value) = "" Then
Range("R" & Myrow).Value = "Blank"
Range("L" & Myrow).Value = Range("K" & Myrow).Value
Thanks in advance,