Macro to take to last line and insert new row with number

[Rodney]

Hi all,

I'm looking to get a macro to take me to the last active row in column A and
then insert a new row, with the next number then appearing in the first cell
of the newly inserted row (for example cell A27 has the number 27 in it and I
want the number 28 to automatically appear in cell A28 when the new row is
inserted). The trouble is that my rows above have merged cells in Column A -
does this cause a problem when trying to get the next number to appear when I
insert a new row? I also want the new row to have black text around all the
boxes from column A to column Z.

Is this possible?

Many thanks,
Rod

 

[Trevor Shuttleworth]

Rod

When you say, you have merged cells in Column A, are they vertically or
horizontally merged cells? If they are vertically merged cells, it could
make a difference to determining the last row.

What do you mean by "black text around all boxes"? Do you mean a black
border?

The following code will only work as I think you want it to if the cells are
*not* merged vertically.

'*** Start of Code ***
Sub Test()
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
'MsgBox LastRow ' for testing

Range("A" & LastRow + 1).Value = Range("A" & LastRow).Value + 1

With Range("A" & LastRow + 1 & ":Z" & LastRow + 1)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideVertical).LineStyle = xlNone
End With
End Sub
'*** End of Code***

Regards

Trevor

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Don Guillett]

File returned to OP with this to copy and insert 3 rows & increment number.

Option Explicit
Sub newrowswithformattingandnumberSAS()
Application.ScreenUpdating = False
Dim myrow As Long
myrow = Cells(Rows.Count, 2).End(xlUp).Row
Rows(myrow).Resize(3).Copy
Cells(myrow + 3, 1).PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, SkipBlanks:=False
Cells(myrow + 3, 2) = Cells(myrow, 2) + 1
Application.CutCopyMode = False
Cells(myrow + 3, 1).Select
Application.ScreenUpdating = True
End Sub