Excel VBA在最后一行和第列选择范围 [英] Excel VBA select range at last row and column

问题描述

我正在尝试创建一个宏，用于选择最后一行和最后一列的范围。

I'm trying to create a macro that selects the range of last row and last column.

例如我想从我的电子表格中选择1，2，3，4，然后删除选择。

E.g. I want to select 1, 2, 3, 4 from my spreadsheet and then delete the selection.

数据：

John  | 10 | 10 | 10
Smith | 5  | 5  | 5
Fred  | 8  | 8  | 8 
1     | 2  | 3  | 4

这是我的代码，它只选择A列的最后一行。 （选择1并删除它）。我需要它选择1到4并删除整行。

Here is my code, it only selects the the last row on the A column. (selects 1 and deletes it). I need it to select 1 to 4 and delete the whole row.

Range("A" & Rows.Count).End(xlUp).Select
Selection.Delete Shift:=xlUp

推荐答案

这是你正在尝试的吗？我已经评论过代码，所以你不会有任何理解它。

Is this what you are trying? I have commented the code so that you will not have any problem understanding it.

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

    '~~> Set this to the relevant worksheet
    Set ws = [Sheet1]

    With ws
        '~~> Get the last row and last column
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

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

        With rng
            Debug.Print .Address
            '
            '~~> What ever you want to do with the address
            '
        End With
    End With
End Sub

BTW我假设 LastRow 对于所有行都是一样的，对于列也是一样的。如果不是这样，那么您将使用 .Find 来查找最后一行和最后一列。您可能希望看到这个

BTW I am assuming that LastRow is the same for all rows and same goes for the columns. If that is not the case then you will have use .Find to find the Last Row and the Last Column. You might want to see THIS

这篇关于Excel VBA在最后一行和第列选择范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！