macro for data range that varies on text file import

[gnrboyd]

I need some assistance on setting up a macro to accomplish some tasks on a select range of cells that will vary from data source. I am a record and try to tweak macro user with almost no VBA knowledge.

I need to import a txt or csv file into a worksheet and have the macro take care of functions such as formatting, text to columns, delete rows, etc. There are 3 basic ranges of data. The first few rows are header info such as name, account number, etc. The second range are charge records several columns wide. At the end of the data, there are a few rows of total information such as sales tax, total, prior balance, etc. Each of the 3 ranges are different and need to be treated separately when formatting and breaking out columns.

I can do the first set of data because it is always in the same place. I just don't know the beginning and ending points on the other 2 sets of data because they will always be in differnet places.

In the past, I have come accross similar problems that I handle some of the steps manually but this macro will be used by many others and I need it to work without much explanation or it defeats the purpose.

This is a very shortened example of the type of data I am working with.

1 2 3 4 5
A Customer Name
B Address
C City, State, Zip
D Account Number
E Billing Period xx/xx/xx
F - Blank -
G Serial # Type Category Cost
H Serial # Type Category Cost
I Serial # Type Category Cost
J Serial # Type Category Cost
K Serial # Type Category Cost
L New Charges
M Prior Balance
N Payments
O Total Due

Thank you for any assistance you can provide.

 

[Ziggy12]

I hate downloads formated for printing. Anyway record the text to colum bit

The next thing is to find the data area you want. The trick is to find some common element to it or something that excludes it from the stuff you don't want. You know what the first few header rows are and you know what the last few rows are so can you code a loop that will select each cell down one colum of the list till it finds the end of the criteria

e.g.
Rownumn = 3 'Start of Data less 1
do
RowNum = Rownum +1
Loop Until cells(Rownum,1) <> "What you're want" or changes to something else.
range(cells(1,1),cells(activecell.row,100)).select

you can then copy it somewhere else to work on it or delete all the rows except the data are or flag the rows not wanted with the macro e.g. if cells(rownum,1)<>"What you want" then cells(rownum,2)="Not Required" and sort it.

From what you say I'd copy it somewhere else (so you've still got the source data to check against) and put my own colum headings on it as excel prefers single row colum labels.