Excel Row Limit

G

Guest

Guest
Hi,
Might sound rediculous.. but I have some data that exceeds Excel's 65536 row limit. Basically the data is some sort of database table. Any ideas how to get past this limitation. Our company is on a tight budget, so my boss doesn't want to invest in other software. Need to do some reports.
Any ideas guys?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Well, I am looking into that possibility.. but I am 'programming' the sheet -> its to be used by ppl with limited Excel knowledge. Since I am using VBA, would splitting it be possible?? Plus the report would make extensive use of array formulas + formulas. Will it break if multipe sheets are used?? Here's the loading code anyway..

Sub Load(strFileName As String)

Workbooks.OpenText FileName:=strFileName _
, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(30, 1), Array(43, 1), Array(57, 1), Array(74, 1), Array(79, 1), _
Array(84, 1), Array(95, 1), Array(117, 1), Array(133, 1), Array(152, 1), Array(171, 1), _
Array(185, 1), Array(193, 1), Array(197, 1), Array(199, 1), Array(200, 1), Array(201, 1), _
Array(204, 1))
Selection.EntireRow.Insert

End Sub

Sub FormatData()

Columns("B:B").NumberFormat = "0"
Columns("C:C").NumberFormat = "0"
Columns("D:D").NumberFormat = "0"
Columns("G:G").NumberFormat = "dd-mmm-yy"
Columns("H:H").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("I:I").NumberFormat = "#,##0.00;[Red]#,##0.00"
Columns("J:J").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("K:K").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("M:M").NumberFormat = "dd-mmm-yy"

End Sub

Sub FormatColumn()

Range("A1").FormulaR1C1 = "NAME"

Range("B1").FormulaR1C1 = "PV"

Range("C1").FormulaR1C1 = "BV"

Range("D1").FormulaR1C1 = "LOAN #"

Range("E1").FormulaR1C1 = "STATE"

Range("F1").FormulaR1C1 = "RESP COLL"

Range("G1").FormulaR1C1 = "DUE DATE"

Range("I1").FormulaR1C1 = "BALANCE"

Range("J1").FormulaR1C1 = "OVL AMT"

Range("K1").FormulaR1C1 = "TOTAL DUE"

Range("L1").FormulaR1C1 = "TRXN"

Range("M1").FormulaR1C1 = "DATE"

Range("N1").FormulaR1C1 = "TIME"

Range("O1").FormulaR1C1 = "ACTIVITY"

Range("P1").FormulaR1C1 = "PLACE"

Range("Q1").FormulaR1C1 = "CONTACT"

Range("R1").FormulaR1C1 = "RTE"

Range("S1").FormulaR1C1 = "LINE 10"

Range("T1").FormulaR1C1 = "ABV"

With Range("A1:T1")
.Font.ColorIndex = 2
.Interior.ColorIndex = 1
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With

Columns("A:T").AutoFit

End Sub
 
Upvote 0
Had you thought about connecting direct to the data source via SQL and doing a query at Data Base level rather than dumping all the data in a spreadsheet.

If you can get access to your db you can use the Data->pivot table and choose the access exteral DB option. You will need to set up ODBC drivers to connect if you don't have them done already. From there it's just a matter of selecting the tables that contain the data you want and creating a query.

Let me know if your interested, I've done this a few times and it's quick, easy and cheap!

Cheers
Matt
 
Upvote 0
Hi..
Sorry.. went home to get some sleep.. Well, actually, am working in a bank. As part of an automation project, we are looking into ways to automate the Site Visit assignment. All the assignments are done using a mainframe system. The following morning, we get a dump of all transactions done. I would love to have SQL support... But I am interested.. maybe if you could show me how to do it, I could persuade my bosses to inves in SQL... Thanks..
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top