inserting rows at bottom
 

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

inserting rows at bottom
 

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 wrote in message
...
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

inserting rows at bottom
 

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
' 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).SelectedShee ts
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 wrote in message
...
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

inserting rows at bottom
 

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" wrote in message
...
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
' 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).SelectedShee ts
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 wrote in message
...
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

inserting rows at bottom
 

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" wrote in message
...
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
' 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).SelectedShee ts
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 wrote in message
...
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