Hi Experts

I think this is the third time I'm asking for help for this problem an
never got any replies :(. So I went and worked on the way I put m
question to the board.

Well here goes again.

I am loading data from a sql database into an excel file using th
following query:

Code
-------------------
cn.Open cString
For m = 1 To curMonth
ProductbyDealer = "select a.sp_name as branch,a.gy_mat_desc as material_descr, a.group_descr,a.mat_grp_desc as brand, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat) as value_excl_vat from other_tyres_by_dealer a left outer join cb b on"
ProductbyDealer = ProductbyDealer + " a.sp_name = b.sp_name and a.gy_mat_desc = b.gy_mat_desc and month(inv_date) = " & m & " and year(inv_date) = 2004 group by a.sp_name,a.gy_mat_desc, a.group_descr,a.mat_grp_desc"
ProductbyDealer = ProductbyDealer + " union select a.sp_name,a.gy_mat_desc, a.group_descr,'ULTIMATE', sum(b.qty), sum(b.value_incl_vat - b.vat)"
ProductbyDealer = ProductbyDealer + " from gy_tyres_by_dealer a left outer join cb b on a.sp_name = b.sp_name and a.gy_mat_desc = b.gy_mat_desc"
ProductbyDealer = ProductbyDealer + " and month(inv_date) = " & m & " and year(inv_date) = 2004 group by a.sp_name,a.gy_mat_desc, a.group_descr,a.mat_grp_desc order by a.sp_name"

If rs.State = -1 Then rs.Close

rs.Open ProductbyDealer, cn, adOpenKeyset, adLockOptimistic, adCmdText

'dump data into excel
Call PopulatePage2(m
-------------------

This is the code I use to populate the page:

Code
-------------------
Public Function PopulatePage2(Optional y As Integer = 1)
Dim RowCount, HeaderRow, curColumn, m

HeaderRow = 2
RowCount = HeaderRow
While rs.EOF = False
RowCount = RowCount + 1

For m = 0 To rs.Fields.Count - 1
curColumn = IIf(m = 0, 1, ((y - 1) * (rs.Fields.Count - 1)) + m + 1)

If m = 1 Then msSheet.Cells(HeaderRow - 1, curColumn + 1).Value = MonthName(y) _

msSheet.Cells(HeaderRow - 1, curColumn).Font.Color = vbYellow
msSheet.Cells(HeaderRow - 1, curColumn).Font.Size = 12
msSheet.Cells(HeaderRow - 1, curColumn).Font.Bold = True
msSheet.Cells(HeaderRow - 1, curColumn).Interior.Color = vbBlue

msSheet.Cells(HeaderRow, curColumn).Value = ProperCase(Replace(rs.Fields(m).Name, "_", " "))
msSheet.Cells(HeaderRow, curColumn).Font.Color = vbYellow
msSheet.Cells(HeaderRow, curColumn).Font.Size = 12
msSheet.Cells(HeaderRow, curColumn).Font.Bold = True
msSheet.Cells(HeaderRow, curColumn).Interior.Color = vbBlue
msSheet.Cells(RowCount, curColumn).Value = rs.Fields(m).Value
Next m
rs.MoveNext
Wend
End Functio
-------------------

My problem is that certain columns are continually repeated which i
making the populating of the sheet take too long. My solution was t
write in the following code which works fine, but a lot of time woul
be saved if I just cutout the unneccessary columns from the ver
begining when loading the data:

Code
-------------------
For i = 5 To 35 Step 2
ActiveSheet.Columns(i).Select
Selection.Delete Shift:=xlToLeft
Nex
-------------------

I have attached a screenshot of the spreadsheet.

Please Please help.

I would appreciate it very much.

Kind Regard

Attachment filename: test2.gif
Download attachment: http://www.excelforum.com/attachment.php?postid=62812
--
Message posted from http://www.ExcelForum.com