PDA

View Full Version : Solved: Fill Down to cells that appear blank



Lande
10-27-2008, 06:29 AM
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
10-27-2008, 07:07 AM
I am struggling to see what this codes does/is supposed to do?

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

Lande
10-27-2008, 08:19 AM
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
10-27-2008, 09:56 AM
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
10-27-2008, 10:29 AM
Like this?

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

Lande
10-27-2008, 10:54 AM
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!