Need better macro

[revinfo]

I so seldom have to edit my macros anymore, since most have worked now
for several years with little trouble. However, the macro below fills
in blank cells that have a different number of rows each month. Until
this month, Range commands all began at A11, which worked fine until I
had to insert three rows above it. I didn't think of the impact and of
course the macro referencing cells in row 11 caused this macro to
really make quite a mess.

Below, there are different sets of copy opearations that take place
based on certain colums, and that shouldn't change. However for
anything below that now references row 14, how can I defend the
integrity of the macro if I need to insert or delete rows in the
future? There is no range name defined right now that begins on row
14, but there is a row 11 range name called START, if you wanted to
offset it that way like I did it on the 3rd line of the macro below. I
appreciate the help. I just do these every day anymore to remember all
the tricks available.

Bruce



Sub Fillinblanks()
Dim cRows As Long
cRows = Cells(14, 7).End(xlDown).Row
Range("Start").Offset(3, 0).Select
Range(ActiveCell, ActiveCell.Offset(0, 5)).Select
Range("a14:F14").AutoFill Destination:=Range("A14:F14", Cells(cRows,
"A"))
Range("A14:F14", Cells(cRows, "A")).Copy
Range("A14:F14", Cells(cRows, "A")).PasteSpecial Paste:=xlValues
Range("H14:I14").AutoFill Destination:=Range("H14:I14", Cells(cRows,
"H"))
Range("H14:I14", Cells(cRows, "H")).Copy
Range("H14:I14", Cells(cRows, "H")).PasteSpecial Paste:=xlValues
Range("L14:O14").AutoFill Destination:=Range("L14:O14", Cells(cRows,
"L"))
Range("L14:O14", Cells(cRows, "L")).Copy
Range("L14:O14", Cells(cRows, "L")).PasteSpecial Paste:=xlValues
Range("P14").Copy
Range("P15").Select
Range(Selection, Selection.Offset(cRows - 14, 0)).PasteSpecial
Range("R14:AV14").AutoFill Destination:=Range("R14:AV14", Cells(cRows,
"R"))
Range("R14:AV14", Cells(cRows, "R")).Copy
Range("R14:AV14", Cells(cRows, "R")).PasteSpecial Paste:=xlValues
Range("K14").Copy
Range("K15").Select
Range(Selection, Selection.Offset(cRows - 11, 0)).PasteSpecial
Application.CutCopyMode = False
Range("A1").Offset(cRows, 0).Select
Range("LuseEntry").Copy
ActiveCell.PasteSpecial xlPasteValues
End Sub

 

[Bob Phillips]

Can you give us some sample data that we can try it on?

 

[Don Guillett]

Sub flexible()
sr = Columns("A").Find(What:="start", LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row + 3
'MsgBox sr
lr = Cells(sr, 1).End(xlDown).Row
'MsgBox lr
'========
Cells(sr, "a").AutoFill Range(Cells(sr, "a"), Cells(sr, "f"))
Range(Cells(sr, "A"), Cells(sr, "F")).Value = _
Range(Cells(sr, "A"), Cells(sr, "F")).Value
'etc for the other ranges

End Sub



cRows = Cells(14, 7).End(xlDown).Row
Range("Start").Offset(3, 0).Select
Range(ActiveCell, ActiveCell.Offset(0, 5)).Select

Range("a14:F14").AutoFill Destination:=Range("A14:F14", Cells(cRows,"A"))
Range("A14:F14", Cells(cRows, "A")).Copy
Range("A14:F14", Cells(cRows, "A")).PasteSpecial Paste:=xlValues

Range("H14:I14").AutoFill Destination:=Range("H14:I14", Cells(cRows,"H"))
Range("H14:I14", Cells(cRows, "H")).Copy
Range("H14:I14", Cells(cRows, "H")).PasteSpecial Paste:=xlValues
Range("L14:O14").AutoFill Destination:=Range("L14:O14", Cells(cRows,"L"))
Range("L14:O14", Cells(cRows, "L")).Copy
Range("L14:O14", Cells(cRows, "L")).PasteSpecial Paste:=xlValues
Range("P14").Copy
Range("P15").Select
Range(Selection, Selection.Offset(cRows - 14, 0)).PasteSpecial
Range("R14:AV14").AutoFill Destination:=Range("R14:AV14", Cells(cRows,"R"))
Range("R14:AV14", Cells(cRows, "R")).Copy
Range("R14:AV14", Cells(cRows, "R")).PasteSpecial Paste:=xlValues
Range("K14").Copy
Range("K15").Select
Range(Selection, Selection.Offset(cRows - 11, 0)).PasteSpecial
Application.CutCopyMode = False
Range("A1").Offset(cRows, 0).Select
Range("LuseEntry").Copy
ActiveCell.PasteSpecial xlPasteValues
End Sub

 

[revinfo]

Can you give us some sample data that we can try it on?

Ok, Bob, here is some data, since I don't know how I can attach a
spreadsheet in this group within the google groups interface.

1 182 470 0 51079000 51079000-30 001 001
1 182 470 0 10104000 10104000-30 001 001


Explanaton: Columns A to F in this case extend through the "0" values
just after the "470"; meaning there are two columns with no values in
them in this case. Columns A to F are copied down as far as the number
of rows in crows. H and I are blank data fields, but populted with ' '
in them just B and C are. J is populated earlier through a separate
function, so its here just so you can see the variation in types of
data. Some fields are formatted numerically, and others are formatted
with characters. I had a problem with the columns K and L on the
autofill because 001 is a constant and I can't have it increment by 1
each row. That is why column L, the 2nd 001 just got changed to be
like the copy opearation shown in this macro in column K, so it won't
increment by 1 like it was doing.

I could of attached the whole spreadsheet to make it easier to play
with, but I didn't know how to do it in this interface.