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 |
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 |
All times are GMT +1. The time now is 10:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com