VBA 将列中的所有单元格转换为文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10584950/
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
VBA convert all cells in column to type text
提问by Aeropher
I'm using VBA to do some further formatting to a generated CSV file that's always in the same format. I have a problem with my For Each Loop. the loop deletes an entire row if there is more than one blank row which can be determined from the first column alone.
我正在使用 VBA 对生成的 CSV 文件进行一些进一步的格式化，该文件始终采用相同的格式。我的 For Each 循环有问题。如果有多个空白行可以仅从第一列确定，则循环将删除整行。
Dim rowCount As Integer
For Each cell In Columns("A").Cells
rowCount = rowCount + 1
'
' Delete blank row
'
If cell = "" And cell.Offset(1, 0) = "" Then
Rows(rowCount + 1).EntireRow.Delete
spaceCount = 0
End If
Next
At some point the value in the loop one of the calls does not have a value of "", it's just empty and causes it to crash. To solve this I think that changing the type of the cell to text before that compare would work but I can't figure out how (no intellisense!!!)
在某些时候，循环中的一个调用的值没有“”的值，它只是空的并导致它崩溃。为了解决这个问题，我认为在比较之前将单元格的类型更改为文本会起作用，但我不知道如何（没有智能感知！！！）
So how do you convert a cell type in VBA or how else would I solve the problem?
那么如何在 VBA 中转换单元格类型或者我将如何解决问题？
Thanks.
谢谢。
回答by CaBieberach
Use cell.Valueinstead of the cell.Textas it will evaluate the value of the cell regardless of the formating. Press F1 over .Valueand .Textto read more about both.
使用cell.Value代替 ，cell.Text因为无论格式如何，它都会评估单元格的值。按 F1.Value和.Text阅读有关两者的更多信息。
Be carefull with the statement For Each cell In Columns("A").Cellsas you will test every row in the sheet (over a million in Excel 2010) and it could make Excel to crash.
请注意该语句，For Each cell In Columns("A").Cells因为您将测试工作表中的每一行（在 Excel 2010 中超过一百万），这可能会使 Excel 崩溃。
Edit:
编辑：
Consider also the funcion TRIM. It removes every empty space before and after a string. If in the cell there is a white space " "; it will look like empty for the human eye, but it has a space inside therefore is different than "". If you want to treat it like an empty cell, then try:
还要考虑函数TRIM。它删除字符串前后的每个空格。如果单元格中有空格“”；对于人眼来说，它看起来像是空的，但它内部有一个空格，因此与“”不同。如果您想将其视为空单元格，请尝试：
If Trim(cell.value) = "" then
回答by dylnmc
As @andy (https://stackoverflow.com/users/1248931/andy-holaday) said in a comment, For Eachis definitely the way to go. This even allows for there to be spaces in between lines.
正如@andy ( https://stackoverflow.com/users/1248931/andy-holaday) 在评论中所说，For Each绝对是要走的路。这甚至允许行之间有空格。
Example code:
示例代码：
Sub ListFirstCol()
Worksheets("Sheet1").Activate
Range("A1").Activate
For Each cell In Application.Intersect(Range("A:A"), Worksheets("Sheet1").UsedRange)
MsgBox (cell)
Next
End Sub
Thanks Andy!
谢谢安迪！
