使用最后一列作为范围（F：LastColumn） [英] Use Last Column for a Range(F:LastColumn)

问题描述

我试图使用我的范围的最后一列： WS.range（F2：LastCol& LastRow）.Cells 我的子作品

I am trying to use Last column for my range: WS.range("F2:LastCol" & LastRow).Cells my sub works for

WS.range（F2：K& LastRow）.Cells 但 Last Column 是动态的，不断变化

WS.range("F2:K" & LastRow).Cells but the Last Column is dynamic and keeps changing

感谢

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

推荐答案

LastCol是一个数字，语法你'用于指定范围需要一个字母。

LastCol is a number, and the syntax you're using to specify the range requires a letter.

您可以找到列号的列号，并将其传递给您的范围定义，如下所示：

You can find out the column letter for the column number and pass it in to your range definition like this:

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 Function

这篇关于使用最后一列作为范围（F：LastColumn）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！