在电子表格中查找最后使用的单元格 [英] Finding the last used cell in a spreadsheet

问题描述

我需要帮助自动化一个MIS报告。

I need ur help to automate one MIS Report.

说明：每天我们将通过下载GL中的未清项目来分发管理信息系统。每日订单项总数不一样。我已经创建了一个宏，我已经从第一个项目到最后一个（5800个订单项）选择了范围。但是在第2天，我会增加到6500，因为我只选择范围为5800..system显示错误。我们可以修改一些东西，比如10000项吗？或系统应根据交易次数自动选择整个范围。

Description: Daily we will circulate MIS by downloading the open items lying in GL. Total no of line items will vary on daily basis. I have created a macro where i have selected Range from first item to last one(5800 Line items). But on day 2 it would increase to 6500 as i have only selected range as 5800..system is showing an error. Instead of specific range selection can we fix some thing like 10000 Items ? or System should automatically select the entire range based on the number of transactions.

范围（C5881）。选择

推荐答案

如果您在下载之前删除工作表中的行UsedRange（）应该在下载后返回您要查找的内容。请注意，您不能只删除前几天行的内容，需要删除行。评论中的链接显示了一种找到最后一行的方法，只需删除剩下空行的单元格的内容。

If you DELETE the rows in your sheet before the download UsedRange() should return what you are looking for after the download. Note that you cannot just delete the contents of the previous days rows, the rows need to be deleted. The link in the comment shows a way to find the "last row" if you just delete the contents of the cells leaving empty rows behind.

这里是一些使用Siddharth的测试代码方法和UsedRange：

Here's some test code using Siddharth's method and UsedRange:

Sub x()
With Sheets("Sheet1")
    Debug.Print .UsedRange.Address
    Debug.Print .UsedRange.Cells.Count
    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
    Else
        lastrow = 1
    End If
    Debug.Print lastrow
    .UsedRange.Select
End With

End Sub

请注意，UsedRange（）将对格式化的单元格和空的单元格进行计数。将一些单元格添加到sheet1并运行代码。删除底行中单元格的内容，看看结果如何变化。

Note that UsedRange() will count cells with formatting and cells that are empty. Add some cells to sheet1 and run the code. Delete the contents of cells in the bottom rows and see how the result change.

这篇关于在电子表格中查找最后使用的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！