By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,188 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,188 IT Pros & Developers. It's quick & easy.

export to excel then merge cells perf issue

P: 10

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!
Jun 18 '08 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,295

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

Jun 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.