"Add/Remove Rows Code" adds rows on grouped sheets, but won't remove rows.

[Conan Kelly]

Hello all,

I have code that I use to add or remove rows (at the location of the active
cell) on a work sheet. I've been trying to modify it so it will work with a
set of Grouped worksheets. It works fine when I am adding rows, but when I
need to remove rows, it only removes rows from the active sheet in the
grouped sheets. I have to manually remove (or rerun the code) on each
individual sheet.

Why won't this remove rows from all grouped sheets? What do I need to do to
get this to work?



Sub Input_InsertRows()
'
' Macro1 Macro
' Macro recorded 11/10/2005 by Conan Kelly
'

'
Dim pintCurrentRow As Long
Dim pintLastRow As Long
'Dim pintInputRow As Long
Dim pstrLastRow As String

pstrLastRow = InputBox("Please enter the total number of records:" &
vbCrLf & vbCrLf & "(Current number of rows: " & ActiveCell.Row - 1 & ")",
"Insert/Delete Rows", , 11500, 9500)
If pstrLastRow = "" Then
Exit Sub
Else
Do Until IsNumeric(pstrLastRow)
MsgBox "The value you have entered is not a number!",
vbInformation, "Error"
pstrLastRow = InputBox("Please enter the total number of
records:", "Insert/Delete Rows")
Loop
pintLastRow = pstrLastRow
'pintInputRow = pstrLastRow
'pintLastRow = Abs(pintInputRow)
End If

pintCurrentRow = ActiveCell.Row

'If pintInputRow > 0 Then
If pintLastRow > pintCurrentRow - 1 Then
Range(Rows(pintCurrentRow), Rows(pintLastRow)).Select
Selection.Insert Shift:=xlDown
'ElseIf pintInputRow < 0 Then
ElseIf pintLastRow < pintCurrentRow - 1 Then
Range(Rows(pintCurrentRow), Rows(pintLastRow + 2)).Select
Selection.EntireRow.Delete
Else
MsgBox "Error! Please Try Again"
End If

Cells(pintLastRow + 1, 1).Select

If Application.ActiveWindow.SelectedSheets.Count > 1 Then
Application.ActiveWindow.SelectedSheets(1).Select
End If
End Sub


Thanks for any help anyone can provide,

Conan Kelly

 

[Jim Cone]

Conan,
When I try manually deleting rows on grouped sheets, only rows on
the top sheet are deleted. It appears that is the way it works.
I doubt you can get around that.
You can loop thru the sheets and accomplish what you want...
*(untested)*
'---
Sub Input_InsertRows()
' Macro recorded 11/10/2005 by Conan Kelly
Dim pintCurrentRow As Long
Dim pintLastRow As Long
'Dim pintInputRow As Long
Dim pstrLastRow As String

Dim WS As Worksheet

pstrLastRow = InputBox("Please enter the total number of records:" & _
vbCrLf & vbCrLf & "(Current number of rows: " & _
ActiveCell.Row - 1 & ")", "Insert/Delete Rows", , 11500, 9500)
If pstrLastRow = "" Then
Exit Sub
Else
Do Until IsNumeric(pstrLastRow)
MsgBox "The value you have entered is not a number!", _
vbInformation, "Error"
pstrLastRow = InputBox("Please enter the total number of records:", _
"Insert/Delete Rows")
Loop

For Each WS In ActiveWindow.SelectedSheets
WS.Activate
pintLastRow = pstrLastRow
'pintInputRow = pstrLastRow
'pintLastRow = Abs(pintInputRow)
pintCurrentRow = ActiveCell.Row
'If pintInputRow > 0 Then
If pintLastRow > pintCurrentRow - 1 Then
Range(Rows(pintCurrentRow), Rows(pintLastRow)).Select
Selection.Insert Shift:=xlDown
'ElseIf pintInputRow < 0 Then
ElseIf pintLastRow < pintCurrentRow - 1 Then
Range(Rows(pintCurrentRow), Rows(pintLastRow + 2)).Select
Selection.EntireRow.Delete
Else
MsgBox "Error! Please Try Again"
End If

Cells(pintLastRow + 1, 1).Select
Next 'WS

End If
If Application.ActiveWindow.SelectedSheets.Count > 1 Then
Application.ActiveWindow.SelectedSheets(1).Select
End If
End Sub
---
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Conan Kelly"
wrote in message
Hello all,
I have code that I use to add or remove rows (at the location of the active
cell) on a work sheet. I've been trying to modify it so it will work with a
set of Grouped worksheets. It works fine when I am adding rows, but when I
need to remove rows, it only removes rows from the active sheet in the
grouped sheets. I have to manually remove (or rerun the code) on each
individual sheet.
Why won't this remove rows from all grouped sheets? What do I need to do to
get this to work?
Sub Input_InsertRows()
'
' Macro1 Macro
' Macro recorded 11/10/2005 by Conan Kelly
Dim pintCurrentRow As Long
Dim pintLastRow As Long
'Dim pintInputRow As Long
Dim pstrLastRow As String

pstrLastRow = InputBox("Please enter the total number of records:" &
vbCrLf & vbCrLf & "(Current number of rows: " & ActiveCell.Row - 1 & ")",
"Insert/Delete Rows", , 11500, 9500)
If pstrLastRow = "" Then
Exit Sub
Else
Do Until IsNumeric(pstrLastRow)
MsgBox "The value you have entered is not a number!",
vbInformation, "Error"
pstrLastRow = InputBox("Please enter the total number of
records:", "Insert/Delete Rows")
Loop
pintLastRow = pstrLastRow
'pintInputRow = pstrLastRow
'pintLastRow = Abs(pintInputRow)
End If

pintCurrentRow = ActiveCell.Row

'If pintInputRow > 0 Then
If pintLastRow > pintCurrentRow - 1 Then
Range(Rows(pintCurrentRow), Rows(pintLastRow)).Select
Selection.Insert Shift:=xlDown
'ElseIf pintInputRow < 0 Then
ElseIf pintLastRow < pintCurrentRow - 1 Then
Range(Rows(pintCurrentRow), Rows(pintLastRow + 2)).Select
Selection.EntireRow.Delete
Else
MsgBox "Error! Please Try Again"
End If

Cells(pintLastRow + 1, 1).Select

If Application.ActiveWindow.SelectedSheets.Count > 1 Then
Application.ActiveWindow.SelectedSheets(1).Select
End If
End Sub


Thanks for any help anyone can provide,

Conan Kelly