Copy down missing data

[Dave Shultz]

I want to write a macro to copy down “Holiday†in column A until the row
where “Vacation†appears. And then copy down “Vacation†in the subsequent
rows. And the in column B, I want to copy down “Team A†until the next team
is mentioned. However, I don’t want the macro applied to the columns where I
have the # of hours listed.

Suggestions?

Type Team Name Jan Feb Mar Apr
Holiday Team A Tom 16 8
Dick 16 8
Harry 16 8 12
Team B Sue 16
Kim 16 8
Sam 16 8
Team C George 16 8
John 16 8
Jim 8
Vacation Team A Tom 8
Dick 8
Harry
Team B Sue 8
Kim
Sam 8
Team C George 8
John 8
Jim

 

[Jacob Skaria]

Enter the formula in say J2
=J1

Select column A.
Press F5. Click Special
From GoTo window select Blanks.
Right click>Paste Special>Formulas..

If this post helps click Yes

 

[alexrs2k]

Hi, you don't need a macro to do that, just follow these instructions:
Select de Range with the Data (CTRL+A), then Edit->Go to (CTRL+G), then
Special->Blanks and click OK. Now press F2 and write =CR where CR- the column
and row from which you want to replicate (copy) the data, for instance =A1,
and press CTRL+Enter and that's it, your data will be copied as you wanted.

 

[Jim Thomlinson]

Shouldn't they have copied the formula at some point???

Select Cell A3
Select the columns A:C
F5 -> Special... | Blanks
(all blank cells should now be highlighted)
Type = <up arrow>
Ctrl + Enter
Copy PasteSpecial Value

 

[Jacob Skaria]

Thanks Jim for pointing that out..

Dave, The below method will work only if the cells are genuine blanks...(if
the data is imported from some other source it may not be the case.)

--Select a unused cell say E2
--Enter the formula in cell E2 which references the top cell (=E1)
--Copy the cell
--Keeping the copy select the data range in ColA and ColB
--Press F5. From Goto window select blanks
--This will select all blanks.. Now Right click>PasteSpecial>Formulas. will
fill in with the data you need..
--Once done you can select col A > Copy >PasteSpecial>Values to turn
formulas to actual values

If the above doesnt work then try the below macro..

Sub MyMacro()
Dim lngRow As Long
For lngRow = 2 To Cells(Rows.Count, "C").End(xlUp).Row
If Trim(Range("A" & lngRow)) = "" Then _
Range("A" & lngRow) = Range("A" & lngRow - 1)
If Trim(Range("B" & lngRow)) = "" Then _
Range("B" & lngRow) = Range("B" & lngRow - 1)
Next
End Sub



If this post helps click Yes