Solved: Fill Down to cells that appear blank

[Lande]

Hello,

My problem is that I am trying to use a fill down macro and for one column, where I had previously used a formula to populate it, the values are not being properly filled down.

I am using the following to fill down to blank cells:

Columns("A:C").Select
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

This works fine, except for column A. In column A, I had previously used a formula to fill the cells. I then deleted all but every ten or so cells. I then pasted special so that it appears as if all but every tenth cell or so is populated and the others are blank, with no formulas:

Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[1]=""A"",RC[1]=""B""),RC[2],""XXX"")"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A269"), Type:=xlFillDefault
Range("A1:A269").Select
Columns("A:A").Select
Selection.Replace What:="XXX", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Columns("A:A").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The problem is that it looks like Excel is not reading those cells as blank. They are not being filled in.

Thank you in advance for your help.

[Bob Phillips]

I am struggling to see what this codes does/is supposed to do?

Can you give an English description, and post a workbook?

[Lande]

I am attaching a sample workbook.

My main objective is to get each row to have a cell telling which "heading" it is tied to. I am doing this by:

1. Insert Column A.
2. In column A, I am providing a formula that says: If Column B has "A" or "B" in it, then copy "HeadingX" to column A.
3. Copy and paste the values in column A (I don't know if this is necessary, but I thought it might help Excel to recognize the blank cells as actually empty).
4. Fill down all of the empty cells (between populated cells) in Columns A through C.

In the 4th step, the empty cells in column A are not being filled in.

Thanks!

[georgiboy]

Can you post the same workbook but with a sheet with how it looks before the macro is run and a sheet with how you would like it to look? A befor and after if you like.

[georgiboy]

Like this?

[VBA]Sub Filler()
Dim MyRange As Range, rCell As Range
Dim HRow As Integer

Range("A:A").Insert

Set MyRange = Range("B1:B500")


For Each rCell In MyRange.Cells


'to define header row
If rCell.Value = "A" Then
HRow = rCell.Row
Else
If rCell.Value = "B" Then HRow = rCell.Row
End If

If rCell.Offset(, 3).Value = "" Then
rCell.Value = ""
Else
rCell.Offset(, -1).Value = Range("C" & HRow).Value
End If


Next rCell[/VBA]

[Lande]

That works! And much more efficient! I still need to fill down the blank cells in columns B and C, but I have working code for that. Thanks!