vba 将范围设置为最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16361207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set range to last row
提问by xyz
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 将永远运行
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
谢谢
采纳答案by Santosh
Try below code : You may also refer this linkfor 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
回答by glh
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
