Excel VBA Change Cell Value From Empty Lines Only To Blank Value

Question

I am new to VBA. I have searched my challenge online but still cannot figure it out.

Below please find my challenge: I have a table with 3 columns. Column A, B and C. I have 3 conditions to locate the specific row values I am looking for.

1. when column A = "Completed"
2. when column B not blank
3. when column C has no text contents (it has empty lines not blank value, so cannot use ="")

with these three conditions, the very first row (we can say columnC/row1) in my table example is what I am looking for. But in my data, the cells like columnC/row1 have empty lines but only empty lines (no text contents). I would like to automatically delete all the empty lines for those cells and make them blank value. The number of empty line varies. It may have 2 empty lines or 3 empty lines. Any solution would be much appreciated.

Column A	Column B	Column C
Completed	Value
Completed	Value	value
Completed

Below please find my code:

Sub RemoveEmptyLines()

    Dim LastRow As Long
    
    With Worksheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    For i = 1 To LastRow
        With Worksheets("Sheet1")
            If .Cells(i, 1).Value = "Completed" And .Cells(i, 2).Value <> "" And .Cells(i, 3).Value = "?" & Chr(10) & "" & Chr(10) & "" Then
                .Cells(i, 3).FormulaR1C1 = ""
            End If
        End With
    Next i
End Sub

Answer 1

Please, try the next iteration way:

 'your code
  For i = 1 To lastrow
        With Worksheets("Sheet1")
            If .cells(i, 1).value = "Completed" And .cells(i, 2).value <> "" And _
                   .cells(i, 3).value = String(Len(.cells(i, 3).value), vbLf) Then
                .cells(i, 3).value = "": .cells(i, 3).value = .cells(i, 3).value 'to update the cell format
            End If
        End With
    Next i

String(Len(.cells(i, 3).value) means only vbLf characters...