查找范围中分配的最后一列 [英] Find last column assigned in Range

问题描述

我需要找出定义范围内最后一列的列:

I need to find out the column that is the last column in a range that is defined with:

Set RngSource = ActiveWorkbook.ActiveSheet.UsedRange

推荐答案

第一件事

切勿使用 UsedRange 来设置范围.我已经在这里解释了为什么你不应该使用 <代码>使用范围

Never use UsedRange to set your range. I have explained it HERE as to why you shouldn't use UsedRange

Set RngSource = ActiveWorkbook.ActiveSheet.UsedRange

找到包含数据的最后一列和包含数据的最后一行，然后设置您的范围.因此，您将不会出现从范围中找到最后一列的问题.这是一个例子

Find the Last Column that has data and the Last Row which has data and then set your range. So your question of finding the last column from the range will not arise. Here is an example

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lRow As Long, lCol As Long

    '~~> Set your worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            '~~> Find Last Row
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row

            '~~> Find Last Column
            lCol = .Cells.Find(What:="*", _
                   After:=.Range("A1"), _
                   Lookat:=xlPart, _
                   LookIn:=xlFormulas, _
                   SearchOrder:=xlByColumns, _
                   SearchDirection:=xlPrevious, _
                   MatchCase:=False).Column
        Else
            lRow = 1: lCol = 1
        End If

        '~~> Set your range
        Set rng = .Range(.Cells(1, 1), .Cells(lRow, lCol))

        Debug.Print rng.Address
    End With
End Sub

这篇关于查找范围中分配的最后一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！