Excel Macro
 

Hi,
I have a regular task that I do on a pivottable that looks like this (but
longer):

No Code Total
1 DN 841.92
PRP08 0
PRP30 0
2 RDB 0.00
RTQ 0.00
RVW 15,636.98
3 PRP42 3,435.76
Grand Total 672,328.19

I paste this info into a database which requires the number to be repeated
on each line.

At the moment I use a macro to copy the data down to the next entry as shown
below starting in the cell that has No in:
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.filldown

and in the macro the above code is repeated 4 times and I keep repeating the
macro until it gets near to the end of the list.

Ideally I would like the macro to continue repeating the code above until it
gets to the grand total row and then stop.
I am sure this could be done using a "Do while" or similar statement. Could
anyone help please?

Thanks.

Excel Macro
 

try:

Select the cells in the first column, starting with the first data cell
(1) down to the last required cell

F5 | Special | Blanks | OK | Type =
Press the up arrow | Ctrl-Enter.

HTH


"Michael" wrote:

Hi,
I have a regular task that I do on a pivottable that looks like this (but
longer):

No Code Total
1 DN 841.92
PRP08 0
PRP30 0
2 RDB 0.00
RTQ 0.00
RVW 15,636.98
3 PRP42 3,435.76
Grand Total 672,328.19

I paste this info into a database which requires the number to be repeated
on each line.

At the moment I use a macro to copy the data down to the next entry as shown
below starting in the cell that has No in:
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.filldown

and in the macro the above code is repeated 4 times and I keep repeating the
macro until it gets near to the end of the list.

Ideally I would like the macro to continue repeating the code above until it
gets to the grand total row and then stop.
I am sure this could be done using a "Do while" or similar statement. Could
anyone help please?

Thanks.

Excel Macro
 

fantastic, thank you

"Toppers" wrote:

try:

Select the cells in the first column, starting with the first data cell
(1) down to the last required cell

F5 | Special | Blanks | OK | Type =
Press the up arrow | Ctrl-Enter.

HTH


"Michael" wrote:

Hi,
I have a regular task that I do on a pivottable that looks like this (but
longer):

No Code Total
1 DN 841.92
PRP08 0
PRP30 0
2 RDB 0.00
RTQ 0.00
RVW 15,636.98
3 PRP42 3,435.76
Grand Total 672,328.19

I paste this info into a database which requires the number to be repeated
on each line.

At the moment I use a macro to copy the data down to the next entry as shown
below starting in the cell that has No in:
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.filldown

and in the macro the above code is repeated 4 times and I keep repeating the
macro until it gets near to the end of the list.

Ideally I would like the macro to continue repeating the code above until it
gets to the grand total row and then stop.
I am sure this could be done using a "Do while" or similar statement. Could
anyone help please?

Thanks.

Excel Macro
 

Hi

I know you have an answer, but heres a bit of code, that will do what you ask:

Public Sub CopyValues()
For Each cell In Selection
If cell.Value = Empty Then
cell.Value = cell.Offset(-1, 0).Value
End If
Next cell
End Sub

This will require you to select the range you want changed and then run the
macro.

HTH.

/Sune

"Michael" wrote:

Hi,
I have a regular task that I do on a pivottable that looks like this (but
longer):

No Code Total
1 DN 841.92
PRP08 0
PRP30 0
2 RDB 0.00
RTQ 0.00
RVW 15,636.98
3 PRP42 3,435.76
Grand Total 672,328.19

I paste this info into a database which requires the number to be repeated
on each line.

At the moment I use a macro to copy the data down to the next entry as shown
below starting in the cell that has No in:
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.filldown

and in the macro the above code is repeated 4 times and I keep repeating the
macro until it gets near to the end of the list.

Ideally I would like the macro to continue repeating the code above until it
gets to the grand total row and then stop.
I am sure this could be done using a "Do while" or similar statement. Could
anyone help please?

Thanks.