"Run-time error '1004'

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 wrote :
> 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

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 at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Jun 13, 8:31*am, GS > wrote:
> icystorm wrote :
>
>
>
>
>
> > 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
>
> 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

On Wednesday, June 13, 2012 8:09:08 AM UTC-5, icystorm wrote:
> 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

icystorm wrote :
> On Jun 13, 8:31*am, GS > wrote:
>> icystorm wrote :
>>
>>
>>
>>
>>
>>> 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
>>
>> 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

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

On Jun 13, 5:18*pm, Don Guillett > wrote:
> On Wednesday, June 13, 2012 8:09:08 AM UTC-5, icystorm wrote:
> > 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

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

On Jun 13, 7:08*pm, icystorm > wrote:
> On Jun 13, 5:18*pm, Don Guillett > wrote:
>
>
>
>
>
> > On Wednesday, June 13, 2012 8:09:08 AM UTC-5, icystorm wrote:
> > > 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
>
> 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