This will give you the column length for each column
for i =cells(1,columns.count).end(xlToLeft).count
lastrow=cells(rows.count,i).end(xlup).row
msgbox lastrow
next i
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Doug" <(E-Mail Removed)> wrote in message
news:aee9563c-1766-4d59-9c72-(E-Mail Removed)...
Hi,
I have a very long Excel spreadsheet, in excess of 13,000 rows,
generated from a network capture. The original datasheet has multiple
columns, but I’m only interested in two:
* Column G: which contains attributes, which are to become the column
headers in the new spreadsheet (e.g. length; timestamp; source address
etc.)
* Column I: which contains data (the variables) from each frame (e.g.
64; 000897; 10.10.0.4 etc.)
Manual transposition works, but is unfeasible for this amount of data,
I’ve seen various posts on automated transposition but I have an
additional issue in that each frame is an inconsistent total length.
That is Frame 01 would be transposed from A1:P1, but Frame 02 would go
from A2:AA2, which appears to be an issue for most VBScripts I’ve
seen. There is however some consistency in that the data I which to
use consistently runs for 46 rows, and the start of the next frame is
flagged by the start attribute “num”.
Therefore the attributes are transposed from Column G to row 1, only
once to form the headers.
IF Field = "frame.number"
THEN transpose to column A;
IF Field = "len"
THEN transpose to column B
AND rename "frame.length";
IF Field = "frame.time"
THEN transpose to column C;
Etc, etc.
And the variables are transposed under the relevant Attribute (e.g.
column header):
IF Field = "frame.number"
THEN transpose from column I to
column A row 2;
REPEAT for row 2 +n
UNTIL END;
IF Field = "len"
THEN transpose from column I to
column B row 2;
REPEAT for row 2 +n
UNTIL END;
IF Field = "frame.time"
THEN transpose from column I to
column C row 2;
REPEAT for row 2 +n
UNTIL END;
What I then end up with is a new spreadsheet that has:
frame.number frame.length frame.time Further
Attributes ........
1 60
000000000 ..........
2 64
000000002 ..........
3 62
000000004 ..........
Further variables
.......
Does anyone have any suggestions as to the best method to attack this
problem?
Thanks
Doug
Similar Threads
