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

  • Thread starter Thread starter Rodney
  • Start date Start date
R

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
 
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
 
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.
 
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
 
Back
Top