"Run-time error '1004'

[icystorm]

Greetings:

In Excel 2007, I wrote a macro to insert a line between all visible
rows. However, one portion of the code fails and I am unable to
isolate the reason.

The specific error is:

Microsoft Visual Basic "Run-time error '1004':
Insert method of Range class failed

The section of the following code that fails is
"Selection.EntireRow.Insert". Any suggestions would be appreciated.
Thank you.

' select range for unique filter and insert rows

Range("A1").Select
Application.CutCopyMode = False
Range("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveSheet.ShowAllData

 

[icystorm]

icystorm wrote :












You're trying to insert rows using a non-contiguous selection. The rows
must be contiguous to do as you're attempting. Try using a loop and
inserting rows one at a time, and specify  'shift:=xlDown'  to place
the new row above.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

Thank you for your suggestion, Garry. I found a procedure that almost
accomplishes what I need, as shown below. However, it requires input
from the user which is unnecessary. How would I modify/define the
strKey section to simply insert a new row between all visible cells in
a single column? The column is already filtered and the visible cells
are selected. Thank you.

Sub AddRows()
Dim strKey As String
Dim lRows As Long

strKey = Application.InputBox(Prompt:="What keyword/number?",
Title:="Add Rows", Type:=1 + 2)
If strKey = vbNullString Then Exit Sub

For lRows = Selection.Columns(1).Cells.Count To 1 Step -1
If Selection.Columns(1).Cells(lRows, 1) = strKey Then
Selection.Columns(1).Cells(lRows, 1).EntireRow.Insert
End If
Next lRows

End Sub

 

[Don Guillett]

Greetings:

In Excel 2007, I wrote a macro to insert a line between all visible
rows. However, one portion of the code fails and I am unable to
isolate the reason.

The specific error is:

Microsoft Visual Basic "Run-time error '1004':
Insert method of Range class failed

The section of the following code that fails is
"Selection.EntireRow.Insert". Any suggestions would be appreciated.
Thank you.

' select range for unique filter and insert rows

Range("A1").Select
Application.CutCopyMode = False
Range("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
ActiveSheet.ShowAllData

something like this

for each c in range("filteredrange").specialcells(xlvisible)
c.entirerow.insert
next c

 

[GS]

icystorm wrote :

Thank you for your suggestion, Garry. I found a procedure that almost
accomplishes what I need, as shown below. However, it requires input
from the user which is unnecessary. How would I modify/define the
strKey section to simply insert a new row between all visible cells in
a single column? The column is already filtered and the visible cells
are selected. Thank you.

Sub AddRows()
Dim strKey As String
Dim lRows As Long

strKey = Application.InputBox(Prompt:="What keyword/number?",
Title:="Add Rows", Type:=1 + 2)
If strKey = vbNullString Then Exit Sub

For lRows = Selection.Columns(1).Cells.Count To 1 Step -1
If Selection.Columns(1).Cells(lRows, 1) = strKey Then
Selection.Columns(1).Cells(lRows, 1).EntireRow.Insert
End If
Next lRows

End Sub

I believe the intent of this code is to obviate the need for filtering,
and so strKey would be the same as your filter criteria. In this case
it could be input OR user can select any cell containing the criteria.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[icystorm]

something like this

for each c in range("filteredrange").specialcells(xlvisible)
c.entirerow.insert
next c

I tried that, but I still need a way to select all the visible cells.
Next, I modified a macro from a similar problem reported years ago,
but the way I have structured it still does not add rows. I think the
problem is where I set rngVisible. Is the syntax incorrect? Thanks.

---

Sub AddRows()

Dim rngVisible As Range
Dim rng As Range

Range("A1").Select
Application.CutCopyMode = False
Range("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

On Error Resume Next
Set rngVisible = Selection.SpecialCells(xlCellTypeVisible).Select
On Error GoTo 0

If Not rngVisible Is Nothing Then
For Each rng In rngVisible.Areas
rng.EntireRow.Insert
Next rng
End If

End Sub

 

[icystorm]

I tried that, but I still need a way to select all the visible cells.
Next, I modified a macro from a similar problem reported years ago,
but the way I have structured it still does not add rows. I think the
problem is where I set rngVisible. Is the syntax incorrect? Thanks.

---

Sub AddRows()

Dim rngVisible As Range
Dim rng As Range

 Range("A1").Select
 Application.CutCopyMode = False
 Range("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
 Range("A2").Select
 Range(Selection, Selection.End(xlDown)).Select

 On Error Resume Next
 Set rngVisible = Selection.SpecialCells(xlCellTypeVisible).Select
 On Error GoTo 0

If Not rngVisible Is Nothing Then
 For Each rng In rngVisible.Areas
 rng.EntireRow.Insert
 Next rng
 End If

End Sub

Thanks to Garry and Don again for your ideas. I have now answered my
own question and the issue is resolved. I do not know if my solution
(below) is "by the book", but it works perfectly for me.

Again, what this does is filters column A for unique values, selects
all visible cells in range A2:A50000, inserts a single row between all
visible cells, then clears the unique filter. I chose row 50000 as the
bottom of the range because I know I will never have that much data in
the column. Ideally, I would be able to select the entire column, but
I could not make it work that way. Regardless, it works perfectly for
me, as is.

' select range, apply unique filter, select visible cells only

Range("A1").Select
Application.CutCopyMode = False
Range("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select

' add row between unique filtered, visible cells

Dim rngVisible As Range
Dim rng As Range

On Error Resume Next
Set rngVisible =
Range("A2:A50000").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rngVisible Is Nothing Then
For Each rng In rngVisible.Areas
rng.EntireRow.Insert
Next rng
End If

' remove unique filter to display all data

ActiveSheet.ShowAllData