vba 如何在excel中使用VBA确定最后一列（带有值的最后一列）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9395715/
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
How to determine the last column(last column with value) using VBA in excel?
提问by user1204868
I have a question wonder if anyone can help me out.
我有一个问题想知道是否有人可以帮助我。
I'm doing a project that requires to get a summary of tests result. My flow is as follow,
我正在做一个需要获取测试结果摘要的项目。我的流程如下，
First, I would need to open my collected test results, then creating a pivot table, after setting accordingly to what i need to view, i will copy the table into another spreadsheet. Over at this spreadsheet, it will change some of the names to the required names and finally, it will be copied to the summary sheet. Then i will need to tabulate the summary in % also.
首先，我需要打开我收集的测试结果，然后创建一个数据透视表，根据我需要查看的内容进行相应设置后，我会将表格复制到另一个电子表格中。在这个电子表格上，它会将一些名称更改为所需的名称，最后，它会被复制到汇总表中。然后我还需要以 % 为单位列出摘要。
I'm facing with an issue here which is, for my collected test results, when i put it into the pivot table, i cant determine the number of columns that will appear. For example
我在这里面临一个问题，即对于我收集的测试结果，当我将其放入数据透视表时，我无法确定将出现的列数。例如
In on example, we can have
在一个例子中，我们可以有
In another case, we can have
在另一种情况下，我们可以有
If u notice the second image has more columns used. Therefore, I was wondering if there is any way that you guys can share with me to determine the last available column with details to be copied.
如果您注意到第二个图像使用了更多列。因此，我想知道你们是否可以与我分享任何方法来确定要复制的详细信息的最后一个可用列。
And can i get the number of the last column so that i can get the summary as well since i need to put it in the form of %.
我能否获得最后一列的编号，以便我也可以获得摘要，因为我需要将其以 %.
At the moment, i'm using "Range(A1:Z1000000)" which is quite impossible for me to do anything as i cant really find the % manually.
目前，我正在使用“范围（A1：Z1000000）”，这对我来说是不可能做任何事情的，因为我无法真正手动找到％。
btw, i need it in VBA code.
顺便说一句，我需要在 VBA 代码中使用它。
Will appreciate for all the opinions and options provided! Thank you in advance!
将不胜感激提供的所有意见和选项！先感谢您！
回答by assylias
This should do the trick:
这应该可以解决问题：
Sub test()
Dim maxColumn As Long
'Create the table here
maxColumn = getMaxUsedColumnNumber("PivotTableSheet")
'do what you need with the number
End Sub
Function getMaxUsedColumnNumber(sheetName As String) As Long
With Sheets(sheetName)
getMaxUsedColumnNumber = .Cells.Find(What:="*", after:=.Range("A1"), LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
End With
End Function
回答by markblandford
You can use .SpecialCells() as follows:
您可以使用 .SpecialCells() 如下：
ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column
Alternatively, take a look at the ColumnRange property of the PivotTable object. It has a Count property which will return the number of columns in the Pivot Table which could also help.
或者，查看数据透视表对象的 ColumnRange 属性。它有一个 Count 属性，它将返回数据透视表中的列数，这也有帮助。
