对范围使用最后一列(F:LastColumn)
对范围使用最后一列(F:LastColumn)
提问于 2013-06-05 21:21:52
我正在尝试使用我的范围的最后一列：WS.range("F2:LastCol" & LastRow).Cells我的sub工作
WS.range("F2:K" & LastRow).Cells，但是Last Column是动态的，并且一直在变化
谢谢
Sub QQ()
Dim LastRow As Long
Dim LastCol As Long
Dim WS As Worksheet
Dim rCell As range
Set WS = Sheets("sheet1")
LastRow = WS.range("F" & WS.Rows.Count).End(xlUp).Row
LastCol = Cells(2, .Columns.Count).End(xlToLeft).Column
'Also tried: LastCol=rgRange.Cells(rgRange.Count).Column
For Each rCell In WS.range("F2:LastCol" & LastRow).Cells
STUFF 'The stuff works for WS.range("F2:K" & LastRow).Cells
End Sub回答 3
Stack Overflow用户
回答已采纳
发布于 2013-06-05 21:39:21
LastCol是一个数字，您用来指定范围的语法需要一个字母。
您可以找到列号的列字母，并将其传递给范围定义，如下所示：
Sub DynamicRange()
Dim startCol As String
Dim startRow As Long
Dim lastRow As Long
Dim lastCol As Long
Dim myCol As String
Dim ws As Worksheet
Dim rng As Range
Dim cell as Range
Set ws = ThisWorkbook.Sheets("Sheet1")
startCol = "F"
startRow = 2
lastRow = ws.Range(startCol & ws.Rows.Count).End(xlUp).Row
lastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
myCol = GetColumnLetter(lastCol)
Set rng = ws.Range(startCol & startRow & ":" & myCol & lastRow)
For Each cell In rng
' do stuff
Next cell
' check the range we've set
Debug.Print rng.Address
End Sub
Function GetColumnLetter(colNum As Long) As String
Dim vArr
vArr = Split(Cells(1, colNum).Address(True, False), "$")
GetColumnLetter = vArr(0)
End FunctionStack Overflow用户
发布于 2013-06-05 21:30:21
请尝试这样定义您的循环：
For Each rCell In WS.range(ws.Range("F2"), Ws.Cells(LastRow, LastCol)).Cells或者，使用此解决方案可以更快捷(如果WS为Activesheet)：
For Each rCell In WS.range("F2", Cells(LastRow, LastCol)).CellsStack Overflow用户
发布于 2018-07-13 16:27:48
dim wb As Workbook
dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("sheet name")
Set ListObj = ws.ListObjects("table name")
If ListObj Is Nothing Then
Set ListObj = ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$15:$Q$16"), , xlYes)
ListObj.Name = "new table"
Else
lastRow = ListObj.Range.Columns(1).cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ListObj.Resize Range("$B$15:W" & lastRow + 2)
End if页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16941083
复制相关文章
相似问题
腾讯云开发者
