vba 使用VBA查找Excel工作表中非空白列的数量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6897347/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding the number of non-blank columns in an Excel sheet using VBA
提问by niko
How do I find the number of used columns in an Excel sheet using VBA?
如何使用 VBA 在 Excel 工作表中查找已使用的列数？
Dim lastRow As Long
lastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastRow
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?
使用上面的 VBA，我可以找到行数。但是如何在给定的 excel 文件中找到列数？
回答by Jean-Fran?ois Corbett
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
This may also be of use to you:
这也可能对您有用：
With Sheet1.UsedRange
MsgBox .Rows.Count & " rows and " & .Columns.Count & " columns"
End With
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 UsedRangeproperty.
但请注意，如果 A 列和/或第 1 行为空白，则这不会产生与上述其他示例相同的结果。有关更多信息，请阅读该UsedRange物业。
回答by Patrick Honorez
Jean-Fran?ois Corbett's answer is perfect. To be exhaustive I would just like to add that with some restrictonsyou could also use UsedRange.Columns.Countor UsedRange.Rows.Count.
The problem is that UsedRange is not always updated when deleting rows/columns (at least until you reopen the workbook).
Jean-Fran?ois Corbett 的回答是完美的。为了详尽无遗，我只想添加一些限制，您也可以使用UsedRange.Columns.Count或UsedRange.Rows.Count。
问题是，在删除行/列时，UsedRange 并不总是更新（至少在您重新打开工作簿之前）。
回答by Patrick Lepelletier
It's possible you forgot a sheet1each time somewhere before the columns.count, or it will count the activesheetcolumns and not the sheet1's.
您可能sheet1每次都在 之前的某个地方忘记了 a columns.count，或者它会计算activesheet列数而不是sheet1's。
Also, shouldn't it be xltoleft instead of xltoright? (Ok it is very late here, but I think I know my right from left) I checked it, you must write xltoleft.
另外，不应该是 xltoleft 而不是 xltoright 吗？（好吧，现在很晚了，但我想我从左到右都知道我的）我检查过，你必须写xltoleft。
lastColumn = Sheet1.Cells(1, sheet1.Columns.Count).End(xlToleft).Column
回答by Noel Cole
This is the answer:
这是答案：
numCols = objSheet.UsedRange.Columns.count
回答by kadrleyn
Result is shown in the following code as column number(8,9 etc.):
结果在以下代码中显示为列号（8,9 等）：
Dim lastColumn As Long
lastColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox lastColumn
Result is shown in the following code as letter(H,I etc.):
结果在以下代码中显示为字母（H、I 等）：
Dim lastColumn As Long
lastColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox Split(Sheet1.Cells(1, lastColumn).Address, "$")(1)
