inserting rows at bottom

[Nick Smith]

Hi,

I've seen a few similar questions, but I need to search down column A from
row X onwards and then insert a new row immediately below the last row
containing data in column A with the same formatting as the row above.

I'm using Excell 2003 SP1.

Can anyone help?

Thanks,

Nick

[macropod]

Hi Nick,

Your problem description is ambiguous:
"insert a new row immediately below the last row containing data in column A
with the same formatting as the row above"
Does this mean you need to find the:
a) last row that has the same formatting as the row above it, then do an
insert, or
b) last row, then do an insert with the same formatting as the row above?

For (b) you could try something like:

Sub InsertRow()
Dim i As Long
With ActiveSheet
For i = Selection.Rows(1) To .UsedRange.Rows.Count + 1
If Len(Cells(i, 1)) = 0 Then Exit For
Next
Rows(i).EntireRow.Insert
End With
End Sub

Cheers


"Nick Smith" <Nick Smith@discussions.microsoft.com> wrote in message
news:633429D4-94CF-42BF-8A8C-66B1BD7C57BC@microsoft.com...
> Hi,
>
> I've seen a few similar questions, but I need to search down column A from
> row X onwards and then insert a new row immediately below the last row
> containing data in column A with the same formatting as the row above.
>
> I'm using Excell 2003 SP1.
>
> Can anyone help?
>
> Thanks,
>
> Nick

[Nick Smith]

Hi,

Thanks for this and yes, (b) is the more correct description - apologies for
that. Perhaps I should try and be clearer.

What I really need is the following:

1. Search down column A from row 14 onwards until it finds 2 blank rows
together
2. Insert a new row immediately below the last row containing data in
column A
3. This new row should have the same formulas and formatting as the row
above (inc. conditional formatting and validations)
4. The new row should contain no cell data save for column A which should
be sequentially one integer higher than the cell above.

Does that make any sense?

Did try the following which does insert nicely, but fails to copy formatting
and copies cell data, i.e. the wrong way round for me!

Sub InsertNewRow()
'
' InsertNewRow Macro
' Macro recorded 20/04/2006 by Ericsson User
'
' Keyboard Shortcut: Ctrl+h
'
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill
'Dim vRows As Integer
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'type 1 is number
If vRows = False Then Exit Sub
End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 20001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee
2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select

End Sub

Thanks,

Nick


"macropod" wrote:

> Hi Nick,
>
> Your problem description is ambiguous:
> "insert a new row immediately below the last row containing data in column A
> with the same formatting as the row above"
> Does this mean you need to find the:
> a) last row that has the same formatting as the row above it, then do an
> insert, or
> b) last row, then do an insert with the same formatting as the row above?
>
> For (b) you could try something like:
>
> Sub InsertRow()
> Dim i As Long
> With ActiveSheet
> For i = Selection.Rows(1) To .UsedRange.Rows.Count + 1
> If Len(Cells(i, 1)) = 0 Then Exit For
> Next
> Rows(i).EntireRow.Insert
> End With
> End Sub
>
> Cheers
>
>
> "Nick Smith" <Nick Smith@discussions.microsoft.com> wrote in message
> news:633429D4-94CF-42BF-8A8C-66B1BD7C57BC@microsoft.com...
> > Hi,
> >
> > I've seen a few similar questions, but I need to search down column A from
> > row X onwards and then insert a new row immediately below the last row
> > containing data in column A with the same formatting as the row above.
> >
> > I'm using Excell 2003 SP1.
> >
> > Can anyone help?
> >
> > Thanks,
> >
> > Nick
>
>
>

[macropod]

Hi Nick,

See if this does what you want:

Sub InsertRow()
Dim i As Long
Dim j As Integer
With ActiveSheet
j = .UsedRange.Columns.Count
For i = 14 To .UsedRange.Rows.Count + 1
If Len(Cells(i, 1)) = 0 And Len(Cells(i + 1, 1)) = 0 Then Exit For
Next i
.Rows(i).EntireRow.Insert
.Range(Cells(i - 1, 1), Cells(i, j)).FillDown
On Error Resume Next
.Rows(i).SpecialCells(xlConstants).ClearContents
.Cells(i, 1).Value = .Cells(i - 1, 1).Value + 1
End With
End Sub

When you said "2 blank rows together", I've assumed you meant "2 blank cells
together in column A" - since you said you wanted to search down that
column.

Cheers


"Nick Smith" <NickSmith@discussions.microsoft.com> wrote in message
news:9DDB06DE-F96C-4FB5-A347-8BDCF3B425EC@microsoft.com...
> Hi,
>
> Thanks for this and yes, (b) is the more correct description - apologies
for
> that. Perhaps I should try and be clearer.
>
> What I really need is the following:
>
> 1. Search down column A from row 14 onwards until it finds 2 blank rows
> together
> 2. Insert a new row immediately below the last row containing data in
> column A
> 3. This new row should have the same formulas and formatting as the row
> above (inc. conditional formatting and validations)
> 4. The new row should contain no cell data save for column A which should
> be sequentially one integer higher than the cell above.
>
> Does that make any sense?
>
> Did try the following which does insert nicely, but fails to copy
formatting
> and copies cell data, i.e. the wrong way round for me!
>
> Sub InsertNewRow()
> '
> ' InsertNewRow Macro
> ' Macro recorded 20/04/2006 by Ericsson User
> '
> ' Keyboard Shortcut: Ctrl+h
> '
> ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
> ' Re: Insert Rows -- 1997/09/24 Mark Hill
> 'Dim vRows As Integer
> ' row selection based on active cell -- rev. 2000-09-02 David McRitchie
> ActiveCell.EntireRow.Select 'So you do not have to preselect entire
row
> If vRows <> 1 Then
> vRows = Application.InputBox(prompt:= _
> "How many rows do you want to add?", Title:="Add Rows", _
> Default:=1, Type:=1) 'type 1 is number
> If vRows = False Then Exit Sub
> End If
>
> 'if you just want to add cells and not entire rows
> 'then delete ".EntireRow" in the following line
>
> 'rev. 20001-01-17 Gary L. Brown, programming, Grouped sheets
> Dim sht As Worksheet, shts() As String, i As Integer
> ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
> Windows(1).SelectedSheets.Count)
> i = 0
> For Each sht In _
> Application.ActiveWorkbook.Windows(1).SelectedSheets
> Sheets(sht.Name).Select
> i = i + 1
> shts(i) = sht.Name
>
> Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
> Resize(rowsize:=vRows).Insert Shift:=xlDown
>
> Selection.AutoFill Selection.Resize( _
> rowsize:=vRows + 1), xlFillDefault
>
> On Error Resume Next 'to handle no constants in range -- John McKee
> 2000/02/01
> ' to remove the non-formulas -- 1998/03/11 Bill Manville
> Selection.Offset(1).Resize(vRows).EntireRow. _
> SpecialCells(xlConstants).ClearContents
> Next sht
> Worksheets(shts).Select
>
> End Sub
>
> Thanks,
>
> Nick
>
>
> "macropod" wrote:
>
> > Hi Nick,
> >
> > Your problem description is ambiguous:
> > "insert a new row immediately below the last row containing data in
column A
> > with the same formatting as the row above"
> > Does this mean you need to find the:
> > a) last row that has the same formatting as the row above it, then do an
> > insert, or
> > b) last row, then do an insert with the same formatting as the row
above?
> >
> > For (b) you could try something like:
> >
> > Sub InsertRow()
> > Dim i As Long
> > With ActiveSheet
> > For i = Selection.Rows(1) To .UsedRange.Rows.Count + 1
> > If Len(Cells(i, 1)) = 0 Then Exit For
> > Next
> > Rows(i).EntireRow.Insert
> > End With
> > End Sub
> >
> > Cheers
> >
> >
> > "Nick Smith" <Nick Smith@discussions.microsoft.com> wrote in message
> > news:633429D4-94CF-42BF-8A8C-66B1BD7C57BC@microsoft.com...
> > > Hi,
> > >
> > > I've seen a few similar questions, but I need to search down column A
from
> > > row X onwards and then insert a new row immediately below the last row
> > > containing data in column A with the same formatting as the row above.
> > >
> > > I'm using Excell 2003 SP1.
> > >
> > > Can anyone help?
> > >
> > > Thanks,
> > >
> > > Nick
> >
> >
> >

[Nick Smith]

Perfect - thank you very much!!


"macropod" wrote:

> Hi Nick,
>
> See if this does what you want:
>
> Sub InsertRow()
> Dim i As Long
> Dim j As Integer
> With ActiveSheet
> j = .UsedRange.Columns.Count
> For i = 14 To .UsedRange.Rows.Count + 1
> If Len(Cells(i, 1)) = 0 And Len(Cells(i + 1, 1)) = 0 Then Exit For
> Next i
> .Rows(i).EntireRow.Insert
> .Range(Cells(i - 1, 1), Cells(i, j)).FillDown
> On Error Resume Next
> .Rows(i).SpecialCells(xlConstants).ClearContents
> .Cells(i, 1).Value = .Cells(i - 1, 1).Value + 1
> End With
> End Sub
>
> When you said "2 blank rows together", I've assumed you meant "2 blank cells
> together in column A" - since you said you wanted to search down that
> column.
>
> Cheers
>
>
> "Nick Smith" <NickSmith@discussions.microsoft.com> wrote in message
> news:9DDB06DE-F96C-4FB5-A347-8BDCF3B425EC@microsoft.com...
> > Hi,
> >
> > Thanks for this and yes, (b) is the more correct description - apologies
> for
> > that. Perhaps I should try and be clearer.
> >
> > What I really need is the following:
> >
> > 1. Search down column A from row 14 onwards until it finds 2 blank rows
> > together
> > 2. Insert a new row immediately below the last row containing data in
> > column A
> > 3. This new row should have the same formulas and formatting as the row
> > above (inc. conditional formatting and validations)
> > 4. The new row should contain no cell data save for column A which should
> > be sequentially one integer higher than the cell above.
> >
> > Does that make any sense?
> >
> > Did try the following which does insert nicely, but fails to copy
> formatting
> > and copies cell data, i.e. the wrong way round for me!
> >
> > Sub InsertNewRow()
> > '
> > ' InsertNewRow Macro
> > ' Macro recorded 20/04/2006 by Ericsson User
> > '
> > ' Keyboard Shortcut: Ctrl+h
> > '
> > ' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
> > ' Re: Insert Rows -- 1997/09/24 Mark Hill
> > 'Dim vRows As Integer
> > ' row selection based on active cell -- rev. 2000-09-02 David McRitchie
> > ActiveCell.EntireRow.Select 'So you do not have to preselect entire
> row
> > If vRows <> 1 Then
> > vRows = Application.InputBox(prompt:= _
> > "How many rows do you want to add?", Title:="Add Rows", _
> > Default:=1, Type:=1) 'type 1 is number
> > If vRows = False Then Exit Sub
> > End If
> >
> > 'if you just want to add cells and not entire rows
> > 'then delete ".EntireRow" in the following line
> >
> > 'rev. 20001-01-17 Gary L. Brown, programming, Grouped sheets
> > Dim sht As Worksheet, shts() As String, i As Integer
> > ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
> > Windows(1).SelectedSheets.Count)
> > i = 0
> > For Each sht In _
> > Application.ActiveWorkbook.Windows(1).SelectedSheets
> > Sheets(sht.Name).Select
> > i = i + 1
> > shts(i) = sht.Name
> >
> > Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
> > Resize(rowsize:=vRows).Insert Shift:=xlDown
> >
> > Selection.AutoFill Selection.Resize( _
> > rowsize:=vRows + 1), xlFillDefault
> >
> > On Error Resume Next 'to handle no constants in range -- John McKee
> > 2000/02/01
> > ' to remove the non-formulas -- 1998/03/11 Bill Manville
> > Selection.Offset(1).Resize(vRows).EntireRow. _
> > SpecialCells(xlConstants).ClearContents
> > Next sht
> > Worksheets(shts).Select
> >
> > End Sub
> >
> > Thanks,
> >
> > Nick
> >
> >
> > "macropod" wrote:
> >
> > > Hi Nick,
> > >
> > > Your problem description is ambiguous:
> > > "insert a new row immediately below the last row containing data in
> column A
> > > with the same formatting as the row above"
> > > Does this mean you need to find the:
> > > a) last row that has the same formatting as the row above it, then do an
> > > insert, or
> > > b) last row, then do an insert with the same formatting as the row
> above?
> > >
> > > For (b) you could try something like:
> > >
> > > Sub InsertRow()
> > > Dim i As Long
> > > With ActiveSheet
> > > For i = Selection.Rows(1) To .UsedRange.Rows.Count + 1
> > > If Len(Cells(i, 1)) = 0 Then Exit For
> > > Next
> > > Rows(i).EntireRow.Insert
> > > End With
> > > End Sub
> > >
> > > Cheers
> > >
> > >
> > > "Nick Smith" <Nick Smith@discussions.microsoft.com> wrote in message
> > > news:633429D4-94CF-42BF-8A8C-66B1BD7C57BC@microsoft.com...
> > > > Hi,
> > > >
> > > > I've seen a few similar questions, but I need to search down column A
> from
> > > > row X onwards and then insert a new row immediately below the last row
> > > > containing data in column A with the same formatting as the row above.
> > > >
> > > > I'm using Excell 2003 SP1.
> > > >
> > > > Can anyone help?
> > > >
> > > > Thanks,
> > > >
> > > > Nick
> > >
> > >
> > >
>
>
>