给定行中的最后一个非空单元格（列）；excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4872512/
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
Last not empty cell (column) in the given row; Excel VBA
提问by
I have an excel sheet in which I need to find the last non empty cellin a specific row.
我有一个 excel 表格，我需要在其中找到特定行中的最后一个非空单元格。
How do I do this?
我该怎么做呢？
The below will select this for me, but it will select the firstnot empty cell, I need the last not empty cell in the row #29.
下面将为我选择这个，但它会选择第一个非空单元格，我需要#29行中的最后一个非空单元格。
Worksheets("DTCs").Range("A29").End(xlToRight).Select
Worksheets("DTCs").Range("A29").End(xlToRight).Select
采纳答案by Hans Olsson
I think it might work just search from the other direction, so something like:
我认为它可能只是从另一个方向搜索，所以像：
Worksheets("DTCs").Range("IV29").End(xlToLeft).Select
Though maybe the IV would need to be changed to something else depending on the version of Excel (this seems to work in 2003).
尽管可能需要根据 Excel 版本将 IV 更改为其他内容（这似乎在 2003 年有效）。
回答by brettdj
I have expanded on my comment above to provide solutions that
我已经扩展了我上面的评论以提供解决方案
- do not use
Select - cater for the last cell in row 1 being used
- cater for the entire row being empty
- cater for the entire row being full
- 不使用
Select - 迎合正在使用的第 1 行中的最后一个单元格
- 迎合整行是空的
- 迎合整排人满为患
The Findmethod in the second code is a far more direct method of establishing the first non-blank cell
在Find在第二码方法是建立第一非空白单元格的一个更为直接的方法
This line Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious)says, start in cell A1 of Sheet "DTCa" then look backwards (ie from the last cell in row 1) in row1 by column looking for anything (the *). This method either find the last non blank or returns Nothing, ie an empty row
这一行Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious)表示，从工作表“DTCa”的单元格 A1 开始，然后在 row1 中逐列向后查看（即从第 1 行的最后一个单元格）查找任何内容（the *）。此方法要么找到最后一个非空白，要么返回Nothing，即一个空行
using xltoLeftwith specific checks
使用xltoLeft与专项检查
Sub Method1()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("DTCs")
If ws.Cells(1, Columns.Count) = vbNullString Then
Set rng1 = ws.Cells(1, Columns.Count).End(xlToLeft)
If rng1.Column <> 1 Then
'return last used cell
MsgBox "rng1 contains " & rng1.Address(0, 0)
Else
If ws.[a1] = vbNullString Then
MsgBox ws.Name & " row1 is completely empty", vbCritical
Else
'true last used cell is A1
MsgBox "rng1 contains " & rng1.Address(0, 0)
End If
End If
Else
'last cell is non-blank
MsgBox ws.Cells(1, Columns.Count) & " contains a value", vbCritical
End If
End Sub
recommended
受到推崇的
Sub Method2()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("DTCs")
Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlFormulas, , xlByColumns, xlPrevious)
If Not rng1 Is Nothing Then
MsgBox "rng1 contains " & rng1.Address(0, 0)
Else
MsgBox ws.Name & " row1 is completely empty", vbCritical
End If
End Sub
