Copying varying values down through rows

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi,

I'd like to copy values from a column down till a new value is encountered
and then start copying that down. Simple I'm sure but I can't seen to get a
start on it.


Here's what I have:

Column A Column B
Value 1 Number
Number
Number
Value 2 Number
Number


Here's what I'd like after running macro:

Column A Column B
Value 1 Number
Value 1 Number
Value 1 Number
Value 2 Number
Value 2 Number


Thanks for the help.
 
Steve,

Here's a method that uses a helper column (which can later be deleted)
Insert a blank column next to B (ie column C is blank)
In C1: = A1
In C2: = IF(A2="",C1,A2)
Copy C2 down for all of your data
Select Column C and Copy it
Paste Special (Values) into column A
Delete column C

Dan E
 
Here's the trick.

1. Select all relevant cells in column A.
2. Press F5 > Special. Click "Blank" and then OK.
3. Press "=" and select the first cell containing Value 1
in column A.
4. Press said:
value.
Click to expand...

HTH
Jason
Atlanta, GA
 
Steve,

Place the cursor in the first cell then run this code.
It should copy the value from the cell into the empty
cells below. When it hits a new cell with a value, this
will load that vaue into x and continue on.
Activecell.CurrentRegion.Rows.Count will count the number
of rows you will need to process.

dim x as double, rwcnt as integer
rwcnt = activecell.currentregion.rows.count
for i = 0 to rwcnt
if not isempty(activecell.offset(i,0) then
x = activecell.value
else
do while isempty(activecell.offset(i,0))
activecell.offset(i,0).value = x
i = i + 1
loop
end if
next i

regards,
Mike
 
Back
Top