使用索引查找带有值的最后一个单元格 [英] Find last cell with values using indexes

问题描述

我尝试在包含值的列中找到最后一个单元格.我知道我可以使用类似的东西:

I try to find the last cell in a column that contains values. I know I can use something like:

Dim findme As Range = excel.Range("A1:A" & lastrow.row)

但是我正在寻找一种不使用此列字符格式的方法.像这样的VBA格式

but I am looking for a way not to use this column-character format. Something like this VBA format

Dim rng as range
with myWorksheet
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    rng = .Range(.Cells(1,1), .Cells(LastRow, 1))
end with

推荐答案

我创建了一个帮助程序类来解决此问题:

I created an helper class to solve this problem:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class FindLastCellByIndex
    Private Shared Function getStringRangeFormat(ByVal colNumber As Long, ByVal rowNumber As Long) As String
        If colNumber > 0 AndAlso colNumber < 27 Then
            Dim c As Char
            c = Convert.ToChar(colNumber + 64)
            Return (c & rowNumber & ":" & c).ToString
        Else
            Return ""
        End If
    End Function

    Public Shared Function getSearchRange(ByRef _sheet As Excel.Worksheet, ByVal Col As Long, ByVal startRow As Long) As Excel.Range
        Dim strColCharSpez As String = getStringRangeFormat(Col, startRow)
       'Build String
        Dim rng As Excel.Range = DirectCast(_sheet.Cells(_sheet.Rows.Count, startRow), Excel.Range)
        Dim t As Long
        t = rng.End(Excel.XlDirection.xlUp).Row

        Return _sheet.Range(strColCharSpez & t)

    End Function
End Class

这是使用它的方式:

Dim rng As Excel.Range = FindLastCellByIndex.getSearchRange(_sheet, 3, 3)

现在，将范围:Range(Cells(3,3)，Cells(LastRow, 3)作为对象.

Now you get the Range: Range(Cells(3,3), Cells(LastRow, 3) as an object.

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