macro change at each blank line

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Unfortunately I dont know enough about the basic language of a macro, I can
record a short macro and make some edits in VB but thats about it.

How do I run a looping macro that at each change in cost centre (col A) I
input the code on each line beneath until I come to a blank line.
So in col A it will say Cost centre - Code (eg 301) Name (eg Christchurch)
The following rows, column A is blank and all the data that applies to 301
is listed from cols B to H in however many rows of data there are. I now
need to sort that info in the other columns so I want to run a macro that
each time the cost centre changes, offset 1 row and take the code from col A
and put that in column A until a blank line appears. Then I'll be able to
manipulate my data by cost centre which I cant currently do. Hope that makes
sense
 
You can do this manually or by macro.

Manually select column A and F5>Special>Blanks>OK.

In activecell(the white one) type an = sign then point to the cell above.

CTRL + ENTER will fill the blanks with the value from above.

With column A still selected.....copy>paste special>values>OK>Esc.

Macro............

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim lastrow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'or
'col = .range("b1").column

Set rng = .UsedRange 'try to reset the lastcell
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(lastrow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.NumberFormat = "General"
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With

End With

End Sub



Gord Dibben MS Excel MVP
 
Back
Top