Automatically insert/delete rows to be x apart

Hi All,

I have a problem formatting an existing Excel spreadsheet into a format
acceptable by a database which needs each NAME cell to be separated by
5 blank rows.

I have the following fields...

NAME, ID, ADDRESS, TELEPHONE/FAX/EMAIL, CONTACT, CODES

(where there can be 1-8 rows of CODES for each NAME)

Can anyone think of a way to automatically delete/insert rows (ignoring
that there may be info in CODES), so that NAME is every 6 rows??

eg

Bob 5 1 / 2 Rodeo Drive 5551245 Jane QWE
Springfield ASD
WA ZXC
76767 QAZ
LKJ
JHG
OIU
HUY
Bob2 6 1 / 2 Rodeo Drive 5551245 Jane QWE
Springfield
WA
76767

You need vba code to do it. How does the data be inserted anyway?
excelmarksway@yahoo.com.au
http://www.geocities.com/excelmarksway


"Mr_Flibble" wrote:

> Hi All,
>
> I have a problem formatting an existing Excel spreadsheet into a format
> acceptable by a database which needs each NAME cell to be separated by
> 5 blank rows.
>
> I have the following fields...
>
> NAME, ID, ADDRESS, TELEPHONE/FAX/EMAIL, CONTACT, CODES
>
> (where there can be 1-8 rows of CODES for each NAME)
>
> Can anyone think of a way to automatically delete/insert rows (ignoring
> that there may be info in CODES), so that NAME is every 6 rows??
>
> eg
>
> Bob 5 1 / 2 Rodeo Drive 5551245 Jane QWE
> Springfield ASD
> WA ZXC
> 76767 QAZ
> LKJ
> JHG
> OIU
> HUY
> Bob2 6 1 / 2 Rodeo Drive 5551245 Jane QWE
> Springfield
> WA
> 76767
>
>

Any suggestions on a macro that could do that?

The data is then saved as a csv then would be imported into an access
database.

exceluserforeman wrote:

> You need vba code to do it. How does the data be inserted anyway?
> excelmarksway@yahoo.com.au
> http://www.geocities.com/excelmarksway
>
>
> "Mr_Flibble" wrote:
>
> > Hi All,
> >
> > I have a problem formatting an existing Excel spreadsheet into a format
> > acceptable by a database which needs each NAME cell to be separated by
> > 5 blank rows.
> >
> > I have the following fields...
> >
> > NAME, ID, ADDRESS, TELEPHONE/FAX/EMAIL, CONTACT, CODES
> >
> > (where there can be 1-8 rows of CODES for each NAME)
> >
> > Can anyone think of a way to automatically delete/insert rows (ignoring
> > that there may be info in CODES), so that NAME is every 6 rows??
> >
> > eg
> >
> > Bob 5 1 / 2 Rodeo Drive 5551245 Jane QWE
> > Springfield ASD
> > WA ZXC
> > 76767 QAZ
> > LKJ
> > JHG
> > OIU
> > HUY
> > Bob2 6 1 / 2 Rodeo Drive 5551245 Jane QWE
> > Springfield
> > WA
> > 76767
> >
> >

You say 5 rows from the name yet 8 rows could be part of the data.

You need to tell me how the data is inserted. Is it imported? Or is the data
generated dependant on user interaction (eg: by userform)?



Due to current time restrictions, I may not be able to respond until early
next week.

Signing off ....





"Mr_Flibble" wrote:

> Any suggestions on a macro that could do that?
>
> The data is then saved as a csv then would be imported into an access
> database.
>
> exceluserforeman wrote:
>
> > You need vba code to do it. How does the data be inserted anyway?
> > excelmarksway@yahoo.com.au
> > http://www.geocities.com/excelmarksway
> >
> >
> > "Mr_Flibble" wrote:
> >
> > > Hi All,
> > >
> > > I have a problem formatting an existing Excel spreadsheet into a format
> > > acceptable by a database which needs each NAME cell to be separated by
> > > 5 blank rows.
> > >
> > > I have the following fields...
> > >
> > > NAME, ID, ADDRESS, TELEPHONE/FAX/EMAIL, CONTACT, CODES
> > >
> > > (where there can be 1-8 rows of CODES for each NAME)
> > >
> > > Can anyone think of a way to automatically delete/insert rows (ignoring
> > > that there may be info in CODES), so that NAME is every 6 rows??
> > >
> > > eg
> > >
> > > Bob 5 1 / 2 Rodeo Drive 5551245 Jane QWE
> > > Springfield ASD
> > > WA ZXC
> > > 76767 QAZ
> > > LKJ
> > > JHG
> > > OIU
> > > HUY
> > > Bob2 6 1 / 2 Rodeo Drive 5551245 Jane QWE
> > > Springfield
> > > WA
> > > 76767
> > >
> > >
>
>

Yes, that database import process only allows for the "Name" row and 5
rows after that. The 6th row needs to be the "Name" row and so forth. I
realise this means discarding CODES fields, but it's the only way.

What I think would work would be a macro that could automatically count
the blank/empty cells between the Name rows and then delete/insert rows
(which may not be completely empty) before the next Name row to make
them 6 rows apart. Are there some functions that sound like they could
do this?

A little more background to the dilemma is that I was given the
spreadsheet with all the existing data in it. If there's some way of
correctly formatting it then I should be able to use an existing import
tool to put it into Access where it will only be used for reference
purposes (ie users will not enter any extra data).

I would be really grateful for any suggestions......

Hello again,

You say that the database import process only allows for the "Name" row and 5
> rows after that. Can't you make it 9 rows after the name? Then you can be assured of the row segmentation.

What type of "database import process" are you using?

If the source file is a .csv or .txt or .dat and not an application
proprietory document like .xls or .doc or .pdf then you can import it with
excel either file open method or Data > Import external data >..

You can then determine the fields yourself.

Otherwise I could write an import file program that reads each line into
specific cell
locations. I need to look at the file first to determine its layout ie
delimiters ...


Example1: "blah,blah,blah"

Example2: "blah" "blah" "blah"

Example3: blah,blah,blah

Example4: blah blah blah

etc......


send to excelmarksway@yahoo.com.au

see my stuff at:
http://www.geocities.com/excelmarksway

- - Mark







"Mr_Flibble" wrote:

> Yes, that database import process only allows for the "Name" row and 5
> rows after that. The 6th row needs to be the "Name" row and so forth. I
> realise this means discarding CODES fields, but it's the only way.
>
> What I think would work would be a macro that could automatically count
> the blank/empty cells between the Name rows and then delete/insert rows
> (which may not be completely empty) before the next Name row to make
> them 6 rows apart. Are there some functions that sound like they could
> do this?
>
> A little more background to the dilemma is that I was given the
> spreadsheet with all the existing data in it. If there's some way of
> correctly formatting it then I should be able to use an existing import
> tool to put it into Access where it will only be used for reference
> purposes (ie users will not enter any extra data).
>
> I would be really grateful for any suggestions......
>
>