khaos
09-16-2010, 03:31 PM
Hey All,
I have a Macro that pulls values from a SQL table and pastes them into a formatted excel sheet. I only need 100 rows but for some reason when I run the Macro, it populates 65,536 rows which is the maximum number of rows for Excel 2003. I was wondering if anyone here could help me out. Below is the code that I have on the Excel sheet.
Workbooks(strCurBook).Worksheets("1.1)").Activate
'Get the Column Headers
Call GetHeader("NewItems", cnTables, "1.1)")
'Query Data
cnTmpRec.Open "select * from Asset.GDP", cnTables, 3, 3
If cnTmpRec.RecordCount <= 0 Then
GoTo endCase
End If
'Copy Results
ActiveSheet.Range("A10").CopyFromRecordset cnTmpRec
'Format Cells
Cells(1, 1).Value = 1
Range("A1").Select
Selection.Copy
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlMultiply, _
skipblanks:=False, Transpose:=False
Cells(1, 1).Value = ""
'alt colors
Range("B12:B13").Select
Selection.Copy
Range("B12", Cells(13, ActiveSheet.UsedRange.Columns.Count)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, _
skipblanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Cells.EntireColumn.AutoFit
Range("A1").Select
I'm assuming it's the formatting/alt colors part that runs the rows all the way down to 65,536. Maybe - Range("A1:A100").Select might help? I've tried setting row limits on this code but it doesn't help. I tried something like "Range(Selection, Selection.End(xlDown)).Offset(100).Select" and it kind of solves the problem but in 3 or 4 rows i have the value "0" entered there for no reason, but that solves not having 65,536 rows.
Hope someone can help me out.
Thanks
I have a Macro that pulls values from a SQL table and pastes them into a formatted excel sheet. I only need 100 rows but for some reason when I run the Macro, it populates 65,536 rows which is the maximum number of rows for Excel 2003. I was wondering if anyone here could help me out. Below is the code that I have on the Excel sheet.
Workbooks(strCurBook).Worksheets("1.1)").Activate
'Get the Column Headers
Call GetHeader("NewItems", cnTables, "1.1)")
'Query Data
cnTmpRec.Open "select * from Asset.GDP", cnTables, 3, 3
If cnTmpRec.RecordCount <= 0 Then
GoTo endCase
End If
'Copy Results
ActiveSheet.Range("A10").CopyFromRecordset cnTmpRec
'Format Cells
Cells(1, 1).Value = 1
Range("A1").Select
Selection.Copy
Range("B10").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlMultiply, _
skipblanks:=False, Transpose:=False
Cells(1, 1).Value = ""
'alt colors
Range("B12:B13").Select
Selection.Copy
Range("B12", Cells(13, ActiveSheet.UsedRange.Columns.Count)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, _
skipblanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Cells.EntireColumn.AutoFit
Range("A1").Select
I'm assuming it's the formatting/alt colors part that runs the rows all the way down to 65,536. Maybe - Range("A1:A100").Select might help? I've tried setting row limits on this code but it doesn't help. I tried something like "Range(Selection, Selection.End(xlDown)).Offset(100).Select" and it kind of solves the problem but in 3 or 4 rows i have the value "0" entered there for no reason, but that solves not having 65,536 rows.
Hope someone can help me out.
Thanks