使用VBA查找Excel表单中的非空白列数 [英] Finding the number of non-blank columns in an Excel sheet using VBA

问题描述

如何使用VBA查找Excel表格中使用的列数？

How do I find the number of used columns in an Excel sheet using VBA?

Dim lastRow As Long
lastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastRow

使用上述VBA，我可以找到行数。但是如何找到我给定的excel文件中的列数？

Using the above VBA I'm able to find the number of rows. But how do I find the number of columns in my given excel file?

推荐答案

您的示例代码获取当前列中最后一个非空白单元格的行号，可以重写如下：

Your example code gets the row number of the last non-blank cell in the current column, and can be rewritten as follows:

Dim lastRow As Long
lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lastRow

然后很容易看到等效的代码来获取当前行中最后一个非空白单元格的列号为：

It is then easy to see that the equivalent code to get the column number of the last non-blank cell in the current row is:

Dim lastColumn As Long
lastColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox lastColumn

这也可能对你有用：

With Sheet1.UsedRange
    MsgBox .Rows.Count & " rows and " & .Columns.Count & " columns"
End With

但请注意，如果列A和/或行1是空白的，那么这不会产生与上述其他示例相同的结果。有关更多信息，请阅读 UsedRange 属性。

but be aware that if column A and/or row 1 are blank, then this will not yield the same result as the other examples above. For more, read up on the UsedRange property.

这篇关于使用VBA查找Excel表单中的非空白列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！