export to excel then merge cells perf issue

[rytsyd]

Hi,

I already have a working code where I have an application that queries to SQL then exports it to excel. It also merge cells that have the same value. The only problem I have is that it takes too long to merge the cells since it needs check each cell per column and row. Below is my sample code:

Dim MergeCellRange As Excel.Range
Dim rowcellcount As Integer
Dim colcellcount As Integer
Dim x As Integer
x = 2
For colcellcount = 1 To columnCount
For rowcellcount = 2 To rowcount + 1
If (ExcelWS.Cells(x, colcellcount).Value2 = ExcelWS.Cells(rowcellcount + 1, colcellcount).Value2) And (ExcelWS.Cells(x, colcellcount).Value2 = ExcelWS.Cells(rowcellcount, colcellcount).Value2) Then
If (ExcelWS.Cells(x, colcellcount).Value2 <> ExcelWS.Cells(rowcellcount + 2, colcellcount).Value2) Then
ExcelWS.Cells.Range(ExcelWS.Cells(x + 1, colcellcount), ExcelWS.Cells(rowcellcount + 1, colcellcount)).Value2 = ""
ExcelWS.Cells.Range(ExcelWS.Cells(x, colcellcount), ExcelWS.Cells(rowcellcount + 1, colcellcount)).Merge()
x = rowcellcount + 2
rowcellcount = rowcellcount + 1
End If
End If
Next rowcellcount
x = 2
Next colcellcount


Currently, it takes 45 minutes for 12400 cells to be exported. Any tip on how to speed it up?

Thanks for your help!

[kadghar]

Sure,

Take a look to this HOWTO about reducing time in Excel's VBA.

HTH