Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
macro change at each blank line
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
macro change at each blank line
You can do this manually or by macro.
Manually select column A and F5SpecialBlanksOK. 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.....copypaste specialvaluesOKEsc. 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 On Mon, 22 Jan 2007 19:51:01 -0800, Somewhat_blonde wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I write macro to sum numbers up to next blank line? | Excel Discussion (Misc queries) | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
Running of Worksheet Change Macro breaks undo functionality. | Excel Discussion (Misc queries) | |||
Can I develop a macro to add blank rows to a list in Excel? | Excel Discussion (Misc queries) | |||
Insert Line Macro | Excel Discussion (Misc queries) |