Removing useless data from Excel spreadsheet

[mike101]

When I open up a data file and have Excel seperate the data, it works fine. However, my problem is that this data file was created and formatted to be printed out, so at they determined how many lines would print out in a page, and on each page put a header that looks like the following:

USE CAUTION: ENSURE DATA IS REASONABLE
DATA MAY NOT BE CURRENT
(UPDATED ON 2003153 AT 4:03:21 GMT)
======= Start data file number ==> 1
WT011530400 2003153 0401
CCAFS/KSC WIND TOWER DATA 0400Z 02 JUN 03
05 MIN 05 MIN 10 MIN
AVERAGE PEAK PEAK TMP
TOWER HGT AV DIR SPD DIR SPD DIR SPD DEV TMP DIF DP RH PRE
FT MIN DEG KTS DEG KTS DEG KTS DEG F F F % MB

I would like to have it find that mess above (right now it cuts it up and puts it in different columns since I am using fixed-width to seperate the data. I would like it to remove this data, but find the timestamp in that header and make a new column next to each data line so I can have my macro import multiple files and add a time stamp so I can make a graph. Anybody have any suggestions?

 

[SkipVought]

Mike,

You are going to have to figgure out how to programatically differentiate between header & detail rows.

Then loop thru all the rows and build a new sheet of tabular data.

Post back your spcific questions.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[VBAjedi]

If this header is always programmatically inserted and is always the same (except for the datestamp & filename), it should be feasible. Depends on how the header rows are laid out.

If all the contents for each header row are in column A, you can just loop through the cells in column "A" checking for cells that contain "Use Caution". Store this "first header row" number in a variable, then advance two rows to put the contents of the cell containing the timestamp into another variable. Starting at the first header row, delete the number of rows the header always occupies. Finally, extract the actual timestamp from the string (use MID if the timestamp is always the same length and at the same place within the string).

Beyond that, Skip is right (as he usually is). You need to be more specific in your questions and description of your data if you want a more specific answer.

VBAjedi