如何指定一张纸来确定"lastrow"? [英] How to specify a sheet to determine 'lastrow'?

问题描述

我正在尝试确定正在运行宏的工作表的最后行".

I am trying to determine 'lastrow' of the sheet that the macro is being run on.

我正在处理两张纸.Sheet1包含约150行数据，Sheet 2仅包含两行数据.

I am working with two sheets. Sheet1 has about 150 rows of data and Sheet 2 only has two.

我希望当我选择Sheet2并分配了lastrow时，它将获取Sheet2中的行数，而不是存储Sheet1中的行数.

I expected that when I selected Sheet2 and assigned lastrow that it would take the count of rows from Sheet2, instead it is storing the row count from sheet1.

sub row_count()
dim lastrow as long
lastrow = Range("A" & Rows.Count).End(xlUp).row

if lastrow = 150 then
    with sheets("sheet2")
        .select
        lastrow = Range("A" & Rows.Count).End(xlUp).row
        msgbox lastrow '<----- Always returns the value of sheet1 instead of sheet2.
    end with

end sub

推荐答案

您正在使用包含块，这意味着程序会看到在"With"和"End With"之间的任何内容都以在关键字"With"之后加上的内容为前缀，因此只能在sheet2上修改代码:

You're using a With block, which means the program sees anything between 'With' and 'End With' as being prefixed by whatever you put after the keyword 'With', so to modify your code in place for sheet2 only:

Sub row_count()

Dim lastrow As Long

lastrow = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row

If lastrow = 150 Then
    With Sheets("sheet2")
    ' .Select = Sheets("sheet2").Select
    .Select
    ' .Range = Sheets("sheet2").Range
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    MsgBox lastrow
End With

End Sub

如果希望代码在当前可见的工作表上运行，则应将其更改为使用

If you want the code to run on the currently visible sheet you should change it to use the ActiveSheet property:

Sub row_count()

Dim lastrow As Long

lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

If lastrow = 150 Then
    With ActiveSheet ' use the currently visible sheet
    .Select
    lastrow = .Range("A" & Rows.Count).End(xlUp).Row
    MsgBox lastrow
End With

End Sub

但是，有一些方法可以改进此代码:为获得灵活性，您可以将工作表作为参数传递.另外，您的结束函数可能会返回第一个使用的行，如果最后使用的行中已经有数据(这与单击最后一行并按Ctrl和向上箭头相同，因此您应该在该行下面的单元格中开始).最后，您无需选择工作表即可获得最后一行:

However, there are some ways to improve this code: for flexibility you could pass the worksheet as a parameter. Also, your End function might return the first used row if there is already data in the last used row (it's the same as clicking in the last row and pressing Ctrl & the up arrow, so you should start in a cell below that). Lastly, you do not need to select the sheet to get the last row:

Sub GetRowCounts()

row_count Sheets("sheet1")
row_count Sheets("sheet2")

End Sub

---

Sub row_count(ws As Worksheet)

Dim lastrow As Long

lastrow = ws.Range("A1000000").End(xlUp).Row

MsgBox lastrow

End Sub

这篇关于如何指定一张纸来确定"lastrow"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！