Solved: VBA for # of Rows Limit

[khaos]

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

[someboddy]

CopyFromRecordset has a maxRows argument.

[khaos]

CopyFromRecordset has a maxRows argument.

Are you suggesting I do something like this:


ActiveSheet.Range("A10:A110").CopyFromRecordset cnTmpRec

specifying the range of rows that I want, or did you mean something else?

[someboddy]

ActiveSheet.Range("A10").CopyFromRecordset cnTmpRec, 100

[khaos]

ActiveSheet.Range("A10").CopyFromRecordset cnTmpRec, 100

Hey someboddy, I tried this and it doesn't work. It still populates 65,636 rows. I assume under another circumstance this may work but since the excel sheet is formatted to colors across the rows, perhaps maybe that's why it doesn't work?

[someboddy]

Try putting Exit Sub before the line Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlMultiply, _ and see what range is selected. Is the entire worksheet selected, or only the area where you want to fill blanks with zeroes?

Also, you said the entire worksheet is populated - but with what? Zeroes? Repeated data? Data you don't want to draw from the database?

[khaos]

It gives me a compile error if I do the Exit Sub before the line with/without the "ActiveSheet.Range("A10").CopyFromRecordset cnTmpRec, 100"

The whole datasheet is populated with rows that have no values in them. I mentioned the 0's got populated when I tried an offset value of 100 rows. the 99th and 100th row had 0's in all the cells. But the rows before them were blank. So i sort of got what I wanted but not quite.
All the rows are formatted to be in colors, right now when i draw data from the SQL database, the data gets imported, but for some reason 65,636 rows of color get populated as well. This increases the file size when it doesn't really need to be increased since there are only about 10-15 rows with data in the SQL table.

Hope I'm not confusing you, but yeah the main idea is to find a way to put a limit on the number of rows populated. Have 100 rows instead of 65,536 or if you can think of a better solution

[someboddy]

What happens if you drop the color formatting from the code?

[khaos]

If I drop it, I have formatted the excel worksheet template before hand. So the values acquired from the SQL table gets imported only to a certain number of rows with the colored fields. But the colored rows don't spread over all the columns. But yeah i get only 50 fields

[someboddy]

OK, I have another idea. try replacing
Range("B12", Cells(13, ActiveSheet.UsedRange.Columns.Count)).Select

Range(Selection, Selection.End(xlDown)).Select

With this


ActiveSheet.UsedRange

Range("B12").select

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

[khaos]

The code works perfect, Thank you very much. It works like a charm. I just had another question. When i import the data another column gets populated at the end (an extra column), which i don't want. Is there a way to delete the last column that gets imported?

Thanks a lot for the code, this helps me a ton. I just have problems with formatting for another worksheet. But this definitely solves a part of my problem.

[someboddy]

CopyFromRecordset also has a maxColumns argument you can use. Or you can delete that column manually: just add this at the end of your code:
SpecialCells(xlLastCell).entireColumn.Delete

[khaos]

It's the weekend, so I'll only be able to try this code on Monday. I'll definitely give it a try then and let you know how it works. Hopefully I get it to work. I have another post on this forum that relates to formatting issues with another worksheet. I would really appreciate it if you could look at the code on that & help me out if possible. The formatting doesn't carry forward to all the cells and stops at a certain column. I will be approaching this problem by trying to do a paste cell format instead of paste cell value. But I'm not quite sure about it. Any help on that would be really appreciated.

Thanks a lot for your help, I'll let you know how this code works.

[khaos]

CopyFromRecordset also has a maxColumns argument you can use. Or you can delete that column manually: just add this at the end of your code:
SpecialCells(xlLastCell).entireColumn.Delete

Hey someboddy, when I add "SpecialCells(xlLastCell).enitreColumn.Delete" to the code and try & execute it, it gives me a compile error near SpecialCells saying that a Sub or Function needs to be defined. Do I need to tell it to delete a specific column? or should it be doing that automatically.

[GTO]

xlCellTypeLastCell

[khaos]

I think i figured it out, it was cells.Specialcells(xlLastcell).entireColumn.Delete