vba 如何找到包含数据的最后一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11926972/
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 do I finding the last column with data?
提问by Neat Machine
I've found this method for finding the last data containing row in a sheet:
我发现这种方法可以在工作表中查找包含行的最后一个数据：
ws.Range("A65536").End(xlUp).row
Is there a similar method for finding the last data containing column in a sheet?
是否有类似的方法来查找工作表中包含列的最后一个数据？
回答by Reafidy
Lots of ways to do this. The most reliable is find.
有很多方法可以做到这一点。最可靠的是找到。
Dim rLastCell As Range
Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
MsgBox ("The last used column is: " & rLastCell.Column)
If you want to find the last column used in a particular row you can use:
如果要查找特定行中使用的最后一列，可以使用：
Dim lColumn As Long
lColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Using used range (less reliable):
使用使用范围（不太可靠）：
Dim lColumn As Long
lColumn = ws.UsedRange.Columns.Count
Using used range wont work if you have no data in column A. See here for another issue with used range:
如果 A 列中没有数据，则使用已使用范围将不起作用。有关已使用范围的另一个问题，请参见此处：
See Hereregarding resetting used range.
有关重置使用范围的信息，请参见此处。
回答by xn1
I know this is old, but I've tested this in many ways and it hasn't let me down yet, unless someone can tell me otherwise.
我知道这已经过时了，但我已经以多种方式对此进行了测试，但它并没有让我失望，除非有人可以告诉我其他情况。
Row number
行号
Row = ws.Cells.Find(What:="*", After:=[A1] , SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Column Letter
列字母
ColumnLetter = Split(ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), "$")(0)
Column Number
列号
ColumnNumber = ws.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
回答by Peter
Try using the code after you active the sheet:
激活工作表后尝试使用代码：
Dim J as integer
J = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
If you use Cells.SpecialCells(xlCellTypeLastCell).Rowonly, the problem will be that the xlCellTypeLastCellinformation will not be updated unless one do a "Save file" action. But use UsedRangewill always update the information in realtime.
如果您Cells.SpecialCells(xlCellTypeLastCell).Row只使用，问题将是xlCellTypeLastCell除非执行“保存文件”操作，否则信息不会更新。但是使用UsedRange总是会实时更新信息。
回答by Stupid_Intern
I think we can modify the UsedRangecode from @Readify'sanswer above to get the last used column even if the starting columns are blank or not.
我认为我们可以修改上面@Readify 的答案中的UsedRange代码，以获取最后使用的列，即使起始列是否为空。
So this lColumn = ws.UsedRange.Columns.Countmodified to
所以这lColumn = ws.UsedRange.Columns.Count修改为
this lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1will give reliable results always
这lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1将始终提供可靠的结果
?Sheet1.UsedRange.Column + Sheet1.UsedRange.Columns.Count - 1
Above line Yields 9in the immediate window.
9立即窗口中的在线收益率。
回答by dapaz
Here's something which might be useful. Selecting the entire column based on a row containing data, in this case i am using 5th row:
这里有一些可能有用的东西。根据包含数据的行选择整列，在这种情况下，我使用第 5 行：
Dim lColumn As Long
lColumn = ActiveSheet.Cells(5, Columns.Count).End(xlToLeft).Column
MsgBox ("The last used column is: " & lColumn)
