Increase columns, decrease rows

D

Dave Peterson

eBay???

The number of rows is set at 65k and the number of columns is 256.

No trades allowed.
 
J

jeff

Hi,

Is there any way you can switch the rows for columns
and vice versa?

Or just break up your data to start spanning multiple
sheets (yuk).

jeff
-----Original Message-----
Newsgroup,

I need to increase the number of spreadsheet columns,
Click to expand...
and would gladly trade in rows. Anyone know how to do
this?
 
D

Dave

jeff,

No, the spreadsheet is fed by an application that creates date/time based information in a .csv file. Around the end of the year the information will fill the existing columns.

I understand the limit of total worksheet cells. It baffles me that such a smart tool is incapable of relabeling some of it's data space to provide more columns and fewer rows. I assumed all along that it could/would -- I used a spreadsheet tool much more rudimentarary than Excel on a minicomputer (bet you haven't heard that term in awhile) 20 years ago that allowed relabeling of the data space to fit the model being developed. Maybe Excel will catch up in the next release.

Oh well, that's what happens when you "assume"!

Thanks - Dave
[email protected]
 
D

Dave Peterson

Maybe you could read the file as text file and do the transposition yourself.

Depending on what your data looks like, this might even work:

Option Explicit
Sub testme()

Dim myLine As String
Dim myFileName As String
Dim FileNum As Long
Dim mySplit As Variant
Dim cCtr As Long
Dim TotalElements As Long

myFileName = "C:\my documents\excel\book5.csv"
FileNum = FreeFile

cCtr = 0
Close FileNum
Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
Line Input #FileNum, myLine
mySplit = Split97(myLine, ",")
cCtr = cCtr + 1
With ActiveSheet
If cCtr > Columns.Count Then
MsgBox "too many columns!"
Exit Do
End If

TotalElements = UBound(mySplit) - LBound(mySplit) + 1

If TotalElements > .Rows.Count Then
MsgBox "too many rows!"
Exit Do
End If

.Cells(1, cCtr).Resize(TotalElements).Value _
= Application.Transpose(mySplit)
End With
Loop
Close FileNum

End Sub
Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As Long _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos > 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function
Public Function Split97(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1, Optional bCompare As _
Long = vbBinaryCompare) As Variant
Dim sRead As String, sOut() As String, nC As Integer
If sDelim = "" Then
Split97 = sIn
End If
sRead = ReadUntil(sIn, sDelim, bCompare)
Do
ReDim Preserve sOut(nC)
sOut(nC) = sRead
nC = nC + 1
If nLimit <> -1 And nC >= nLimit Then Exit Do
sRead = ReadUntil(sIn, sDelim)
Loop While sRead <> ""
ReDim Preserve sOut(nC)
sOut(nC) = sIn
Split97 = sOut
End Function

The readuntil and split97 functions were stolen from the MSKB:
http://support.microsoft.com/default.aspx?scid=kb;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5
 
Top