如何在excel vba中选择全系列 [英] How to select full range in excel vba

问题描述

当我调试一个excel-vba程序时，我知道我的数据范围没有被完全选择。

When I am debugging a excel-vba program I came to know my data range is not completely selected.

下图显示了我的数据模型和我的问题。

Below picture shows my data's model and my problem.

I使用此代码选择整个范围。但是这不能正常工作。

I used this code to select the whole range. But this is not working properly.

Dim rngTemp As Range
Set rngTemp = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
With rngTemp

请帮助我选择整个范围的代码，如上图所示。

Please help me by giving the code for selecting the whole range as given in the figure above.

推荐答案

在您的代码中，您正在通过 xlByRows 。因此，您将获得最后一个单元格的地址，该单元格的数据为 G7 。

In your code you are searching by xlByRows. And hence you are getting the address of the last cell which has data which is G7.

这是你正在尝试的吗？

Sub Sample()
    Dim lastrow As Long, lastcol As Long
    Dim rng As Range

    With Sheets("Sheet1") '<~~ Change this to the relevant sheet
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
            lastcol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column
        Else
            lastrow = 1: lastcol = 1
        End If

        Set rng = .Range("A1:" & _
                         Split(.Cells(, lastcol).Address, "$")(1) & _
                         lastrow)

        MsgBox rng.Address
    End With
End Sub

这篇关于如何在excel vba中选择全系列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！