Do not fire code on header row when using End(xlUp).Row

[detriez]

I use this code to concatenate Column Names and row values values into one field if Column B is in not blank

What's not working: This will run if Column B row 2 is blank and it concatenates my header row
I tried adding a MsgBox to warn the user but, it gets ignored because of this line l

r = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

How can I be sure this code does not fire on my header row?

    lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    
    If Len(lr.Value) < 1 Then
    MsgBox "Last Name (Column B) cannot be blank"
    Else
    

    Application.ScreenUpdating = False
    With Range("AJ2:AJ" & lr)
        .FormulaR1C1 = "=IF(RC[1]<>"""",CONCATENATE((R1C37&"": ""),RC[1])&CHAR(10),"""")&" & _
                       "IF(RC[2]<>"""",CONCATENATE((R1C38&"": ""),RC[2])&CHAR(10),"""")&" & _
                       "IF(RC[3]<>"""",CONCATENATE((R1C39&"": ""),RC[3])&CHAR(10),"""")&" & _
                       "IF(RC[4]<>"""",CONCATENATE((R1C40&"": ""),RC[4])&CHAR(10),"""")&" & _
                       "IF(RC[5]<>"""",CONCATENATE((R1C41&"": ""),RC[5])&CHAR(10),"""")&" & _
                       "IF(RC[6]<>"""",CONCATENATE((R1C42&"": ""),RC[6])&CHAR(10),"""")&" & _
                       "IF(RC[7]<>"""",CONCATENATE((R1C43&"": ""),RC[7])&CHAR(10),"""")&" & _
                       "IF(RC[8]<>"""",CONCATENATE((R1C44&"": ""),RC[8])&CHAR(10),"""")&" & _
                       "IF(RC[9]<>"""",CONCATENATE((R1C45&"": ""),RC[9])&CHAR(10),"""")&" & _
                       "IF(RC[10]<>"""",CONCATENATE((R1C46&"": ""),RC[10])&CHAR(10),"""")&" & _
                       "IF(RC[11]<>"""",CONCATENATE((R1C47&"": ""),RC[11])&CHAR(10),"""")&" & _
                       "IF(RC[12]<>"""",CONCATENATE((R1C48&"": ""),RC[12])&CHAR(10),"""")&" & _
                       "IF(RC[13]<>"""",CONCATENATE((R1C49&"": ""),RC[13])&CHAR(10),"""")&" & _
                       "IF(RC[14]<>"""",CONCATENATE((R1C50&"": ""),RC[14])&CHAR(10),"""")&" & _
                       "IF(RC[15]<>"""",CONCATENATE((R1C51&"": ""),RC[15])&CHAR(10),"""")&" & _
                       "IF(RC[16]<>"""",CONCATENATE((R1C52&"": ""),RC[16])&CHAR(10),"""")&" & _
                       "IF(RC[17]<>"""",CONCATENATE((R1C53&"": ""),RC[17])&CHAR(10),"""")&" & _
                       "IF(RC[18]<>"""",CONCATENATE((R1C54&"": ""),RC[18])&CHAR(10),"""")&" & _
                       "IF(RC[19]<>"""",CONCATENATE((R1C55&"": ""),RC[19])&CHAR(10),"""")&" & _
                       "IF(RC[20]<>"""",CONCATENATE((R1C56&"": ""),RC[20]),"""")" & _
                        ""


                  
                      
        .Value = .Value
    End With
    Application.ScreenUpdating = True
  
 ' Unselect cells
 ' Cells(2, 36).Select
      Range("AJ2").Select
    Selection.Rows.AutoFit

 

[Zot]

Your Header is in row 1, right?

The lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row will end up in row 1 if there is nothing (no data) under Header.

You can always add 1 to it to give row 2. I see some people use Offset(1,0).

Is this what you meant to ask?

 

[detriez]

Thanks Zot, I appreciate your help
Yes, header is row 1.
Adding 1 worked

lr = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1