Range resizing Run Time error “1004”

[julhs]

The below code is a small part of a large Sub; what the section does is a Row Count of the "Filtered" range and the "Temp" range.
What I’m try to do is; IF the “Filter” range is GREATER than the “Temp” range, increase the size of the “Temp” range to accommodate the “Filter” range, BUT the overriding caveat is that “Temp” range has to shift UP!!
(All variables needed/used for the below have been declared earlier)

But it is erroring out, "Run time error 1004 Insert method of Range class failed" on

Range("AK" & FrowTemp + 2 & ":AM" & LrowTemp - 1).Resize(Range("AP" & FrwD - 4).value).Insert Shift:=xlShiftUp

' Next line does a Row Count of "VISIBLE" rows of the "Filter" range.
nrows = Range("AQ" & FrwD + 2 & ":AQ" & LrwD).SpecialCells(xlCellTypeVisible).Count 'This is the "Filtered" range
    Range("AP" & FrwD - 3) = nrows 'This just places the row count in ("AP" & FrwD - 3)
' Next line does a Row Count of "Temp" range
nrows2 = Range("AK" & FrowTemp + 2 & ":AK" & LrowTemp - 1).Count 'This is the "Temp" range
    Range("AP" & FrwD - 2) = nrows2 'This just places the row count in ("AP" & FrwD - 2)
  x = Range("AP" & FrwD - 3) - Range("AP" & FrwD - 2)
    Range("AP" & FrwD - 4) = x 'This just places the difference in the two Row Counts into ("AP" & FrwD - 4)
  If nrows > nrows2 Then 
       MsgBox " Filter range is larger than Temp range "

    Range("AK" & FrowTemp + 2 & ":AM" & LrowTemp - 1).Resize(Range("AP" & FrwD - 4).value).Insert Shift:=xlShiftUp

End If
>>>> continue rest of code
(Copy Filter range to Temp range………….)

Code I write to achieve what I’m after is usually grotesque and long winded but often as not with help I get to what I was trying to do, this is another case in point!

 

[footoo]

What row number is FrwD ?

 

[Alex Blakenburg]

You can't do an Insert Shift:=xlShiftUp.
It doesn't make sense and is not one of the available parameters.

Your insert options are: xlShiftToRight or xlShiftDown.

 

[julhs]

Footoo thanks for reply.
FrwD, LrwD, FrowTemp, LrowTemp are all dynamic rows/ranges.

 

[julhs]

Hi Alex
That being the case; think going to have to just rely on the MsgBox informing me “Temp” range is to small, then manually cut & paste Range("AK" & FrowTemp & ":AM" & LrowTemp - 1) UP by the value in Range("AP" & FrwD – 4), unless there is a way to do that with VBA??

xlShiftToRight or xlShiftDown are not options for me (it would affect other data)

 

[Alex Blakenburg]

manually cut & paste Range("AK" & FrowTemp & ":AM" & LrowTemp - 1) UP by the value in Range("AP" & FrwD – 4), unless there is a way to do that with VBA??

Cut and paste is a different beast.

This is what I cut and paste would look like in VBA:

    Range("AK10:AM12").Cut
    Range("AK7").Insert Shift:=xlDown

 

[julhs]

Ok I think I’ve cracked a work around instead of using Range resizing and used a copy/paste method (Long winded/not such an eloquent manor)!!
New code is:-

Dim i as Integer ' New variable was required, to all previously set
        ' Next line does a Row Count of "VISIBLE" rows of the "Filter" range.
  nrows = Range("AQ" & FrwD + 2 & ":AQ" & LrwD).SpecialCells(xlCellTypeVisible).Count 'This is the "Filtered" range
      Range("AP" & FrwD - 3) = nrows   'This just places the nrows row count in ("AP" & FrwD - 3), but can ultimitly do away with it
        ' Next line does a Row Count of "Temp" range
  nrows2 = Range("AK" & FrowTemp + 2 & ":AK" & LrowTemp - 1).Count                     'This is the "Temp" range
      Range("AP" & FrwD - 2) = nrows2 'This just places the nrows2 row count in ("AP" & FrwD - 2), but can ultimitly do away with it
    i = Range("AP" & FrwD - 3) - Range("AP" & FrwD - 2)
      Range("AP" & FrwD - 4) = i       'This just places the difference in the two Row Counts into ("AP" & FrwD - 4), but can ultimitly do away with it
 If nrows > nrows2 Then
        MsgBox " Filter range is too large for Temp range by this many rows " & i       'Can ultimately do away with this
Set rngCopy = Range("AK" & FrowTemp & ":AM" & FrowTemp)                                 'This is the existing "Temp" range header row
       'Next line copies the existing "Temp" range header row and pastes it to a new row by the value of i
    rngCopy.Copy Range("AK" & FrowTemp - i & ":AM" & FrowTemp - i)          'This is copy/pasting the existing "Temp" header row, by the i value number of rows, 
                                                                            ' to ABOVE the existing header row, so effectively shifting "Temp" header row UP
   Range("AK" & FrowTemp & ":AM" & FrowTemp).Clear                          'This clears the original "Temp" header row                          
End If
'>>>> continue rest of code  (Copy Filter range to Temp range………….)

 

[Alex Blakenburg]

Your initial question is solved by post #3.
To progress this further and simplify your code I think you would be best served by opening an new thread and providing some sample data in XL2BB format and the expected outcome and a more complete version of your code.

A comment on your existing code is that your use of indentation is hindering making it readable rather than helping.
Indentation is supposed to show which code belongs together by:
• Having a code inside the following structures indented to show where the structure starts and finishes eg
if-then-else, Select Case-End Case, For-Next, Do-Loop, With-End With

In the linke below, go to item 4 - Adopt a Consistent Indentation & Spacing Style
For some examples

Maintainable Excel VBA Code: Best Practices

Learn guidelines for maintainable Excel VBA code that will help you build Excel VBA applications that are easier to edit or adapt.

masterofficevba.com

• Indent code where long lines are broken into multiple lines to show the.
This describes that and the above but its a bit slow and around 10 mins

I would expect your code indentation to look more like this:

    Dim i As Integer ' New variable was required, to all previously set
   
    ' Next line does a Row Count of "VISIBLE" rows of the "Filter" range.
    nrows = Range("AQ" & FrwD + 2 & ":AQ" & LrwD).SpecialCells(xlCellTypeVisible).Count 'This is the "Filtered" range
    Range("AP" & FrwD - 3) = nrows   'This just places the nrows row count in ("AP" & FrwD - 3), but can ultimitly do away with it
    ' Next line does a Row Count of "Temp" range
    nrows2 = Range("AK" & FrowTemp + 2 & ":AK" & LrowTemp - 1).Count                     'This is the "Temp" range
    Range("AP" & FrwD - 2) = nrows2 'This just places the nrows2 row count in ("AP" & FrwD - 2), but can ultimitly do away with it
    i = Range("AP" & FrwD - 3) - Range("AP" & FrwD - 2)
    Range("AP" & FrwD - 4) = i       'This just places the difference in the two Row Counts into ("AP" & FrwD - 4), but can ultimitly do away with it
   
    If nrows > nrows2 Then
        MsgBox " Filter range is too large for Temp range by this many rows " & i       'Can ultimately do away with this
        Set rngCopy = Range("AK" & FrowTemp & ":AM" & FrowTemp)                         'This is the existing "Temp" range header row
        'Next line copies the existing "Temp" range header row and pastes it to a new row by the value of i
        rngCopy.Copy Range("AK" & FrowTemp - i & ":AM" & FrowTemp - i)                  'This is copy/pasting the existing "Temp" header row, by the i value number of rows,                                                                               ' to ABOVE the existing header row, so effectively shifting "Temp" header row UP
        Range("AK" & FrowTemp & ":AM" & FrowTemp).Clear                                 'This clears the original "Temp" header row
    End If
    '>>>> continue rest of code  (Copy Filter range to Temp range………….)

 

[julhs]

Alex
Thanks for the the tip and link, I will bear them in mind for the future.
May start a new tread re the Copy/Paste method I cobbled together, yet to decide.
Thanks again for your help.
Julhs

 

[Alex Blakenburg]

You're welcome. Glad we could help.