Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
Let's see if I can explain the problem.
I have a table of information that I want to convert into a pivot table. It's in the right format except that columns A, B and C don't have information in all the cells - only in the first line of each group. eg Line 1 has info in columns A, B, C and D but then lines 2-13 only have the data in column D. Obviously I can just copy the data down in the first 3 columns but when there's hundreds of different groups in different worksheets this gets a bit boring. How can I copy the data in columns A, B and C in line 1 into lines 2-13?
not sure about that !Let's see if I can explain the problem.
could you just sort col a then b then c ??
this kind of thing ? Highlight a column at a time and that fills the blanks in a column with row above value
Sub Fill_Empty()
Dim oRng As Range
Set oRng = Selection
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Font.Bold = False
Selection.FormulaR1C1 = "=R[-1]C"
oRng.Copy
oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
Or - insert extra columns and fill with
=IF(E2= "",A1,E2) etc
IF E2 is empty then give the cell the value of the cell above.
Copy and paste as value.
oh, hang on - you mean you have lots of sheets, each with 13 rows and 4 cols but cols a-c only have row 1 filled, while d has all 13
what's in col d - do you need the contents ? If you do, is it OK for the contents of a-c to alter next to it. Seems an odd ptable to me
what are your headings/labels ?