Use column A to define "Range"

Hi,

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
     End If
     Next Myrow

Thanks in advance,

swjtx99
swjtx99Asked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Dim lngLastRow as Long

lngLastRow = Range("A1048576").End(xlUp).Row
For Myrow = 1 To lngLastRow '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
     End If
     Next Myrow
0
 
swjtx99Author Commented:
Hi Martin Liss,

Thanks for your help.

Regards,

swjtx99
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.