I have a database download that always comes in a frustrating format.
It's effectively outlined, eg. rather than having field names as
column headers and record going down, it has (using as simple an
example as I can describe) the record-ID in A2, Field "A" Name in B2,
Field "A" value in C2, then Field "B" Name in B3, Field "B" Value in
C3, etc. There can be any number of fields in the list (it only
downloads the fields with values), but only the one record-ID at the
top left. After the last field, there's a blank row before the next
record starts.

Anyway, ideally I'd like to macro this up to put the field names as
column headers and then one record per row, but currently that's way
above me.

For now, I'd like help with a simple macro that copies the record-ID
down into every row applicable to that record.

- From the active cell
- find the next non-blank cell (i'm guessing with a loop using
IsEmpty)
- define a range from the active cell down to the next non-blank cell-
minus 2 (because of the blank line)
- Autofill that range with the original activecell's value
- select the next nonblank cell and repeat

Any help would be appreciated.

(getting things back into the original format in order to upload is a
problem I don't even want to consider at the moment!)

Many thanks

First, I'd get rid of those completely empty rows.

Can you pick out a column that always has data in it if the row is really used?

If yes, you can use something like:

with activesheet
on error resume next

..range("x1").entirecolumn.cells.specialcells(xlce lltypeblanks).entirerow.delete
on error goto 0
end with

Those empty rows will cause confusion in data|filter, data|subtotal, data
pivottable--so I'd toss them.

After that, I'd use some code at Debra Dalgleish's site to fill the empty cells
in the columns I needed:
http://contextures.com/xlDataEntry02.html


Math wrote:

I have a database download that always comes in a frustrating format.
It's effectively outlined, eg. rather than having field names as
column headers and record going down, it has (using as simple an
example as I can describe) the record-ID in A2, Field "A" Name in B2,
Field "A" value in C2, then Field "B" Name in B3, Field "B" Value in
C3, etc. There can be any number of fields in the list (it only
downloads the fields with values), but only the one record-ID at the
top left. After the last field, there's a blank row before the next
record starts.

Anyway, ideally I'd like to macro this up to put the field names as
column headers and then one record per row, but currently that's way
above me.

For now, I'd like help with a simple macro that copies the record-ID
down into every row applicable to that record.

- From the active cell
- find the next non-blank cell (i'm guessing with a loop using
IsEmpty)
- define a range from the active cell down to the next non-blank cell-
minus 2 (because of the blank line)
- Autofill that range with the original activecell's value
- select the next nonblank cell and repeat

Any help would be appreciated.

(getting things back into the original format in order to upload is a
problem I don't even want to consider at the moment!)

Many thanks

--

Dave Peterson

On 21 Mar, 12:50, Dave Peterson wrote:
First, I'd get rid of those completely empty rows.

Can you pick out a column that always has data in it if the row is really used?

If yes, you can use something like:

with activesheet
on error resume next

.range("x1").entirecolumn.cells.specialcells(xlcel ltypeblanks).entirerow.de*lete
on error goto 0
end with

Those empty rows will cause confusion in data|filter, data|subtotal, data
pivottable--so I'd toss them.

After that, I'd use some code at Debra Dalgleish's site to fill the empty cells
in the columns I needed:http://contextures.com/xlDataEntry02.html





Math wrote:

I have a database download that always comes in a frustrating format.
It's effectively outlined, eg. rather than having field names as
column headers and record going down, it has (using as simple an
example as I can describe) the record-ID in A2, Field "A" Name in B2,
Field "A" value in C2, then Field "B" Name in B3, Field "B" Value in
C3, etc. There can be any number of fields in the list (it only
downloads the fields with values), but only the one record-ID at the
top left. After the last field, there's a blank row before the next
record starts.

Anyway, ideally I'd like to macro this up to put the field names as
column headers and then one record per row, but currently that's way
above me.

For now, I'd like help with a simple macro that copies the record-ID
down into every row applicable to that record.

- From the active cell
- find the next non-blank cell (i'm guessing with a loop using
IsEmpty)
- define a range from the active cell down to the next non-blank cell-
minus 2 (because of the blank line)
- Autofill that range with the original activecell's value
- select the next nonblank cell and repeat
Excellent - thankyou :)


Any help would be appreciated.

(getting things back into the original format in order to upload is a
problem I don't even want to consider at the moment!)

Many thanks

--

Dave Peterson- Hide quoted text -

- Show quoted text -