VBA-范围行数 [英] VBA - Range.Row.Count

问题描述

我写了一个简单的代码来说明我的困境.

I have written a simple code to illustrate my predicament.

Sub test()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long

    k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
End Sub

这是什么情况:我们计算包含以A1开始的值的行.如果包含值的行数> 1，则该代码将非常有效.但是，如果A1是唯一包含任何值的单元格，则k = 1,048,576，我猜这是Excel中允许的最大行数.

What happens is this: We count the rows which contain values starting at A1. If the number of rows which contain values is > 1 the code works great. However, if A1 is the only cell which contains any value, k = 1,048,576 which I guess is the maximum number of rows allowed in Excel.

为什么k = 1?

图片:

我正在使用的解决方法如下:

The workaround that I'm using is the following:

Sub test()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")

    Dim k As Long

    k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
    If k = 1048576 Then
        k = 1
    End If
    MsgBox (k)
End Sub

因为当具有值的行数为1时，k始终等于1048576.这只是感觉有点愚蠢，不得不做这样的事情.

Since k is always equal to 1048576 when the number of rows with values is 1. It just feels a bit silly having to do something like this.

推荐答案

可能更好的解决方案是从底部开始:

Probably a better solution is work upwards from the bottom:

k=sh.Range("A1048576").end(xlUp).row

这篇关于VBA-范围行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！