CODEWITHSUNDEEP
excelvba
AT555
AT555

Reputation: 1

apply excel vba to entire column instead of single cell

Hi I would like to apply the below vba to the entire column AK instead of just AK1

Sub Tidy_Cell()
Range("AK1") = Replace(Range("AK1"), Chr(13), "")
For x = 1 To Len(Range("AK1"))
    If Mid(Range("AK1"), x, 1) = Chr(10) And Mid(Range("AK1"), x + 1, 1) = Chr(10) Then

    Range("AK1") = Left(Range("AK1"), x) & Mid(Range("AK1"), x + 2)

    End If
    Next
With Range("A1")
    .Value = Mid(.Value, 1)
    .VerticalAlignment = xlTop
End With
End Sub

Thanks a lot for any help!

Upvotes: 0

Views: 345

Answers (1)

T. Nesset
T. Nesset

Reputation: 417

I would put all your code into a Loop that checks column AK

dim lLastUsed As Long
lLastUsed = Cells(1048576, "AK").End(xlUp).Row
For i = 1 to lLastused

    //insert your code here

Next i

Remember every spot you defined it to be Range("AK1") you need to change it to Range("AK" & i) so it ends up something like this:

Sub Tidy_Cell()
Dim lLastUsed As Long
lLastUsed = Cells(1048576, "AK").End(xlUp).Row
For i = 1 to lLastUsed
  Range("AK" & i) = Replace(Range("AK" & i), Chr(13), "")
  For x = 1 To Len(Range("AK" & i))
    If Mid(Range("AK" & i), x, 1) = Chr(10) And Mid(Range("AK" & i), x + 1, 1) = Chr(10) Then

      Range("AK" & i) = Left(Range("AK" & i), x) & Mid(Range("AK" & i), x + 2)

    End If
  Next x
Next i

With Range("A1")
  .Value = Mid(.Value, 1)
  .VerticalAlignment = xlTop
End With
End Sub

Hope this helps you out

Upvotes: 2

Related Questions