Excel 2010 VBA 列长度给定列索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7886343/
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
Excel 2010 VBA Length of Column given column index
提问by Derek
I am finding this surprisingly hard to find an answer for, given its simple description. I hope this is not a duplicate, though it probably is because of its simplicity, and I simply cannot find the answer.
鉴于其简单的描述，我发现这令人惊讶地难以找到答案。我希望这不是重复的，尽管可能是因为它的简单性，我根本找不到答案。
In VBA, I have hundreds of columns, and I'd like to know all their lengths. I know there are exactly "COLS" columns. I want something like this:
在 VBA 中，我有数百个列，我想知道它们的所有长度。我知道确实有“COLS”列。我想要这样的东西：
For i in COLS
length = 'Some one line formula to find the length of column i
'Some code that works with the value of length
Next i
By length I mean the number of non-empty cells... For my specific purposes there will be no blank cells in the columns, and all the cells in the column I wish to count will contain text (all the rest will be empty).
长度我的意思是非空单元格的数量......对于我的特定目的，列中不会有空白单元格，并且我希望计算的列中的所有单元格都将包含文本（其余所有单元格都是空的） .
Help would be much appreciated on this seemingly simple matter!
对这个看似简单的问题的帮助将不胜感激！
Edit: I also want to make this dependent on the column index (which will be 'i' in the loop above). I won't always know the column letter...
编辑：我还想让它依赖于列索引（在上面的循环中将是 'i'）。我不会总是知道列字母...
回答by Fionnuala
This will count textnon-empty cells in VBA:
这将计算VBA 中的文本非空单元格：
For i = 1 To Columns.Count
n = WorksheetFunction.CountA(Columns(i))
Next
回答by brettdj
It is best to use a routine that works automatically from the last row in all Excel versions.
在所有 Excel 版本中，最好使用从最后一行自动运行的例程。
Dim lngRow As Long
lngRow = Cells(Rows.Count, "A").End(xlUp).Row
回答by Marco
I think you could use
我想你可以用
LastRowColA = Range("A" & Rows.Count).End(xlUp).Row
to find last used row in column A.
Navigating through every column you can have what you want...
在 A 列中找到最后使用的行。
浏览每一列，你可以得到你想要的......
回答by Charles Williams
You can find the last non-empty row using Range(large row number, column).End(xlUP).row where large row number should be large enough to always be beyond your last used cell.
您可以使用 Range(large row number, column).End(xlUP).row 找到最后一个非空行，其中大行号应该足够大以始终超出上次使用的单元格。
回答by Jon Egerton
You can use an alternative approach using the worksheet function CountA as follows
您可以使用工作表函数 CountA 使用替代方法，如下所示
Test Data:
测试数据：
Col1 Col2 Col3
A A A
B B
C C C
D D D
E E
VBA function:
VBA 函数：
Sub Test()
Dim sht As Excel.Worksheet
Set sht = Worksheets("Sheet1")
Dim i As Integer
Dim max As Integer
Dim length As Integer
max = 0
For i = 1 To 3
'CountA returns the number of non-blank cells in a range
length = WorksheetFunction.CountA(sht.Columns(i))
If length > max Then max = length
Next i
MsgBox max - 1 ('remove 1 if you have column headers)
End Sub
回答by niko
len = sheet1.Range("B" & Rows.Count).End(xlUp).Row ' gives the count of B column
len = sheet1.Range("A" & Rows.Count).End(xlUp).Row ' gives the count of A column
回答by Patrick Honorez
dim i as integer
For i = 1 To Columns.Count
debug.print i, cells(i, rows.count).end(xlup).row
Next
