访问 VBA 以拆分 Excel 中的所有合并单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17720395/
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
Access VBA to split out ALL merged cell in Excel
提问by Nexus
I need to import some Excel files into Access database. I know how to write the import function in VBA but it is not working as I expected since some merged cell in Excel cause me trouble.
我需要将一些 Excel 文件导入 Access 数据库。我知道如何在 VBA 中编写导入函数，但它没有按我预期的那样工作，因为 Excel 中的某些合并单元格给我带来了麻烦。
So for example, in the Excel spreadsheet
例如，在 Excel 电子表格中
When importing to Access table, it imported row by row . So the 1st row should be 1-pencil-90, and it is correct but the 2nd and 3rd row is empty due to the merged cell. The result is
导入到 Access 表时，它逐行导入。所以第一行应该是 1-pencil-90，它是正确的，但由于合并的单元格，第二行和第三行是空的。结果是
No|Product|Storage
1 | pencil|90
| |23
| |41
But expected result should be:
但预期的结果应该是：
No|Product|Storage
1 | pencil|90
1 | pencil|23
1 | pencil|41
I want the VBA to split the merged cells and also fill in the blank data with the information in the merged cell. Please keep in mind that I got a lot of merged cells and manually change is impossible. I am open to any solution that help me achieve the desire result.
我希望 VBA 拆分合并的单元格，并用合并单元格中的信息填充空白数据。请记住，我有很多合并的单元格，手动更改是不可能的。我对任何帮助我实现预期结果的解决方案持开放态度。
P/S: The aata can be left blank so I can't use conditional check if it blank then look up the above data.
P/S：aata 可以留空，所以我不能使用条件检查它是否为空然后查找上述数据。
回答by chris neilsen
Try this
尝试这个
Sub UnMergeRanges()
Dim cl As Range
Dim rMerged As Range
Dim v As Variant
For Each cl In ActiveSheet.UsedRange
If cl.MergeCells Then
Set rMerged = cl.MergeArea
v = rMerged.Cells(1, 1)
rMerged.MergeCells = False
rMerged = v
End If
Next
End Sub
How it works:
这个怎么运作：
- Loop through cells in the used range
- If the cell is part of a merged range
- Set a Range variable to the Merged Range
- Record the current value of the Top Left cell in the range into a variable
v. This is the value the merged range displays - Unmerge the range
- write the recorded value (
v) into all cells of the now unmerged range
- 循环使用范围内的单元格
- 如果单元格是合并范围的一部分
- 将范围变量设置为合并范围
- 将区域中左上角单元格的当前值记录到一个变量中
v。这是合并范围显示的值 - 取消合并范围
- 将记录的值 (
v) 写入现在未合并范围的所有单元格
