I get text files for work that I need to pull into excel and heavily
manipulate. Since the steps are fairly consistent I would like to set
something up to do this for me. The biggest problem I am having is that the
original text file can vary drastically in size from a few hundred lines to
over 50,000 and since it IS government supplied data it tends to have a lot
of holes.


I usually start by copying the text and pasting it into excel.
The text is sent by various government sources and contains one record per
line.

What I need to do then is to select the just copied text, and paste it to a
new worksheet, label the worksheet and then run "text to columns" to delimit
the text via fixed width.

Then I need to add 5 rows of header data (which is constant across the
board) and
add a column in the middle of the data to show the difference between two
dates (ie Cell 1 €“ Cell 2)

Now here's the hard part, I then need to add find the first 2 completely
blank rows and highlight them yellow and add a =sum and an =count function
both on the new column with the difference in dates.

=sum(entire data set)
=count(entire data set)


Please help!
Thank you!

I think you may be taking the wrong approach. It may be better to use in the
data menu Import Data rather than using text to columns. When you import the
data or copy the data to the new sheet, simply put the data in row 6 rather
than inserting 5 blank rows. Finally, to find the first two blank rows it is
better to start at the last row of the worksheet and look for the 1st row of
data.

LastRow = cells(rows.count,"A").end(xlup).Row

Rows.count = 65636
xlup searchs from 65536 to the last row of data.

From you description it isn't clear what two columns you are comparing and
where are the dates you are comparing.


I would start with a learn Macro (Tools Menu - Macro - Record New Macro).
Perform the steps you need and then post on website so adjustments can be
made.

"Surrealdogma" wrote:

I get text files for work that I need to pull into excel and heavily
manipulate. Since the steps are fairly consistent I would like to set
something up to do this for me. The biggest problem I am having is that the
original text file can vary drastically in size from a few hundred lines to
over 50,000 and since it IS government supplied data it tends to have a lot
of holes.


I usually start by copying the text and pasting it into excel.
The text is sent by various government sources and contains one record per
line.

What I need to do then is to select the just copied text, and paste it to a
new worksheet, label the worksheet and then run "text to columns" to delimit
the text via fixed width.

Then I need to add 5 rows of header data (which is constant across the
board) and
add a column in the middle of the data to show the difference between two
dates (ie Cell 1 €“ Cell 2)

Now here's the hard part, I then need to add find the first 2 completely
blank rows and highlight them yellow and add a =sum and an =count function
both on the new column with the difference in dates.

=sum(entire data set)
=count(entire data set)


Please help!
Thank you!