访问VBA以分解Excel中的所有合并单元格 [英] Access VBA to split out ALL merged cell in Excel

问题描述

我需要将一些Excel文件导入Access数据库。我知道如何在VBA中编写导入功能，但是由于Excel中的某些合并单元格导致麻烦，所以不能正常工作。

所以例如在Excel中电子表格

导入到Access表时，它逐行导入。所以第一行应该是1-pencil-90，它是正确的，但是由于合并的单元格，第2行和第3行是空的。
结果是

 否|产品|存储
 1 |铅笔| 90 
 | | 23 
 | | 41 
  

但预期结果应该是：

 否|产品|存储
 1 |铅笔| 90 
 1 |铅笔| 23 
 1 |铅笔| 41 
  

我希望VBA拆分合并的单元格，并填写空白数据合并单元格中的信息。请记住，我有很多合并的单元格，手动更改是不可能的。我可以帮助我实现想要的结果的任何解决方案。

P / S：aata可以留空，所以我不能使用条件检查然后查看以上数据。

解决方案

尝试这个

  Sub UnMergeRanges（）
 Dim cl As Range 
 Dim rMerged As Range 
 Dim v As Variant 
 
对于每个cl在ActiveSheet中。 UsedRange 
如果cl.MergeCells然后
设置rMerged = cl.MergeArea 
v = rMerged.Cells（1，1）
 rMerged.MergeCells = False 
 rMerged = v 
结束如果
下一个
 End Sub 
  

工作原理： / p>

• 循环使用范围内的单元格


• 如果单元格是合并范围的一部分
  
  
  
  • 将范围变量设置为合并范围
  
  
  • 将范围内的左上角单元格的当前值记录到变量 v 。这是合并范围显示的值
  
  
  • 取消合并范围
  
  
  • 写入记录值（ v ）转换为现在未加入范围的所有单元格
  
  

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.

So for example, in the Excel spreadsheet

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

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.

P/S: The aata can be left blank so I can't use conditional check if it blank then look up the above data.

解决方案

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

这篇关于访问VBA以分解Excel中的所有合并单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！