Fill adjacent column in excel with data from previous column
I have the following vba code. It is not working 100% correctly. I have several columns of data and I would like this code to analyze the cell and the adjacent cell. If the adjacent cell is blank, use the previous cell data. If the adjacent call is not blank, store that data and look at the next adjacent cell and if blank, use previous cell data to populate. What this code is doing is just looking one cell and using it to fill in all of the rest, no matter if they are not blank.
Dim rg As Range, rgg As Range
With ActiveSheet
Set rg = Selection.Cells
If rg.Cells.Count = 1 Then Set rg = .UsedRange
Set rgg = Range(.Cells(2, 3), .Cells(.Rows.Count, .Columns.Count))
Set rg = Intersect(rg, .UsedRange)
Set rg = Intersect(rg, rgg)
End With
On Error Resume Next
Set rg = rg.SpecialCells(xlCellType
On Error GoTo 0
If Not rg Is Nothing Then
rg.FormulaR1C1 = "=RC[-1]"
End If
End Sub
Dim rg As Range, rgg As Range
With ActiveSheet
Set rg = Selection.Cells
If rg.Cells.Count = 1 Then Set rg = .UsedRange
Set rgg = Range(.Cells(2, 3), .Cells(.Rows.Count, .Columns.Count))
Set rg = Intersect(rg, .UsedRange)
Set rg = Intersect(rg, rgg)
End With
On Error Resume Next
Set rg = rg.SpecialCells(xlCellType
On Error GoTo 0
If Not rg Is Nothing Then
rg.FormulaR1C1 = "=RC[-1]"
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Quite true. Another way to handle it is to first sort the data to ensure that the range you need returned via SpecialCells is one contiguous block.
If you need to retain the original order of the data, then you can have the code use a "dummy" column to indicate the original ordering, do the sort, use Special Cells, re-sort to get the original order, and then clear out the dummy column.