Add last Column to Macro

[capson]

I am trying to change the Sub CleanAll() to Sub CleanAll2() but can not get it.

Thanks

Sub CleanAll()


    Dim myArray As Variant
    Dim lastRow As Long
 
    With Sheets("360")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With


    myArray = Sheets("360").Range("B2:CJ" & lastRow)


    For x = LBound(myArray) To UBound(myArray)
        For Y = LBound(myArray, 2) To UBound(myArray, 2)
            myArray(x, Y) = NumberOnly(myArray(x, Y))
        Next Y
    Next x


    Sheets("360").Range("B2:CJ" & lastRow) = myArray
End Sub

Sub CleanAll3()


    Dim myArray As Variant
    Dim lastRow As Long
    Dim lastCol As Long
    Dim WS As Worksheet
    Dim rng As Range


 
  With Sheets("360")
            lastCol = .Cells(8, .Columns.Count).End(xlToLeft).Column
            lastRow = .Cells(.Rows.Count, lastCol).End(xlUp).Row
        End With


For Each rng In Sheets("360").Range(Cells(2, 8), Cells(lastRow, lastCol))


    myArray = Sheets("360").Range(Cells(2, 8), Cells(lastRow, lastCol))


    For x = LBound(myArray) To UBound(myArray)
        For Y = LBound(myArray, 2) To UBound(myArray, 2)
            myArray(x, Y) = NumberOnly(myArray(x, Y))
        Next Y
    Next x


    Sheets("360").Range(Cells(2, 8), Cells(lastRow, lastCol)) = myArray
    Next
End Sub

Function NumberOnly(ByVal strSource As String) As String
    Dim i As Integer
    Dim strResult As String


    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 32, 48 To 57, 65, 78:
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    NumberOnly = strResult


End Function

 

[patel45]

        Sub CleanAll3()
            Dim myArray As Variant
            Dim lastRow As Long
            Dim lastCol As Long
            Dim WS As Worksheet
            Dim rng As Range
        
          With Sheets(1)
                    lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
                    lastRow = .Cells(.Rows.Count, lastCol).End(xlUp).Row
                End With
        For Each rng In Sheets(1).Range(Cells(2, 2), Cells(lastRow, lastCol))
            myArray = Sheets(1).Range(Cells(2, 2), Cells(lastRow, lastCol))
            For x = LBound(myArray) To UBound(myArray)
                For Y = LBound(myArray, 2) To UBound(myArray, 2)
                    myArray(x, Y) = NumberOnly(myArray(x, Y))
                Next Y
            Next x
            Sheets(1).Range(Cells(2, 2), Cells(lastRow, lastCol)) = myArray
            Next
        End Sub

 

[capson]

Thanks for the reply.

When I run the macro with your code excel stops responding i.e the macro never finishes running like there is a infinite loop going on

 

[patel45]

This code works on my testsheet, can you provide a link to your file ?

 

[rollis13]

Cross-post: [SOLVED] Adding LastColumn to Macro not working