CODEWITHSUNDEEP
excelvba
xyz
xyz

Reputation: 2300

Set range to last row

Hello I am trying to set range to last row.

I have

For Each rng In Sheets("Sheet1").Range("B2:CG100").Cells 'adjust sheetname and range accordingly

I tried

For Each rng In Sheets("Sheet1").Range("B2:CG" & lastRow).Cells 'adjust sheetname and range accordingly

But not working

The problem is that the last row changes, if I use entire column the the Sub runs forever

Sub CleanAll()
    Dim rng As Range

    For Each rng In Sheets("Sheet1").Range("B2:CG100").Cells 'adjust sheetname and range accordingly
        rng.Value = NumberOnly(rng.Value)
    Next
End Sub

Thanks

Upvotes: 1

Views: 9082

Answers (2)

glh
glh

Reputation: 4972

For what it's worth this question has been done to death but I use something different:

With ActiveSheet.Cells
    last_column = .Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    last_row = .Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With

Upvotes: 1

Santosh
Santosh

Reputation: 12353

Try below code : You may also refer this link for details.

Sub CleanAll()

        Dim rng As Range

        For Each rng In Sheets("Sheet1").Range("B2").CurrentRegion
            rng.Value = NumberOnly(rng.Value)
        Next
 End Sub

OR 

 Sub CleanAll()

        Dim rng As Range
        Dim lastRow As Long
        Dim lastCol As Long
        Dim colChr As String


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


        For Each rng In Sheets("Sheet1").Range(Cells(2, 2), Cells(lastRow, lastCol))
           rng.Value = NumberOnly(rng.Value)
        Next
End Sub

OR 

Sub CleanAll()

        Dim rng As Range
        Dim lastRow As Long

        With Sheets("sheet1")
            lastRow = .Range("CG" & .Rows.Count).End(xlUp).Row
        End With

        For Each rng In Sheets("Sheet1").Range("B2:CG" & lastRow)
            rng.Value = NumberOnly(rng.Value)
        Next
    End Sub

Upvotes: 1

Related Questions