VBA

[missk1902]

Hi ,

Pls help with the below VBA query. I have two columns (A & B) in excel



A B
XXXX 111
111
111
YYYY 222
222
222
ZZZZ 333
333
333


I need to write a macro that will copy XXXX until it reaches YYYY, copy

YYYY unit it reaches ZZZZ and so on.

Thank you.

 

[Harald Staff]

That is a very useful piece of code indeed. I have a collegue that
copy-pastes summaries from pivot tables very frequently. This code converts
this to complete datasets at a mouseclick. The FormulaR1C1 technique ensures
correct filling of absolute/relative formulae if present.

Sub FillBlanks()
Dim Rng As Range
Dim X As Variant
Dim C As Long, R As Long, Rmax As Long
On Error Resume Next
Set Rng = Application.InputBox("Select column:", _
"Fill in blanks", _
ActiveCell.EntireColumn.Address(, , Application.ReferenceStyle), _
Type:=8)
If Rng Is Nothing Then Exit Sub

C = Rng(1).Column
Rmax = ActiveSheet.UsedRange.Rows.Count
For R = 1 To Rmax
If Cells(R, C).FormulaR1C1 = "" Then
Cells(R, C).FormulaR1C1 = X
Else
X = Cells(R, C).FormulaR1C1
End If
Next
End Sub

HTH. Best wishes Harald