0

I am checking two ranges to see if they have the same size. If the new range is smaller than the old range, then my macro expands the range to match the old.

Sub san_import(owb As String, nwb As String, imt As String)
    Dim orng As Range
    Dim nrng As Range
    Dim r_fix As Long
    Dim c_fix As Long

    Set orng = Workbooks(owb).Names(imt).RefersToRange
    Set nrng = Workbooks(nwb).Names(imt).RefersToRange

    r_fix = orng.Rows.Count - nrng.Rows.Count
    c_fix = orng.Columns.Count - nrng.Columns.Count

    If r_fix > 0 Then    
        With nrng
        .Rows(.Rows.Count + r_fix).EntireRow.Offset(-1, 0).Insert
        .Resize(.Rows.Count + r_fix, .Columns.Count).Name = .Name.Name
        End With

    End If

    If c_fix > 0 Then
        With nrng
        .Columns(.Columns.Count + c_fix).EntireColumn.Offset(-1, 0).Insert
        .Resize(.Rows.Count, .Columns.Count + c_fix).Name = .Name.Name
        End With

    End If

    Set nrng = Workbooks(nwb).Names(imt).RefersToRange
    nrng.ClearContents
    nrng.Value2 = orng.Value2

End Sub

However, I am getting an application defined error at this line:

 .Resize(.Rows.Count + r_fix, .Columns.Count).Name = .Name.Name

Any ideas as to why this happens? Also I am not sure, that the macro successfully inserts new rows, in my tests this doesn't seem the case.

Miles Fett
  • 711
  • 4
  • 17
JoaMika
  • 1,727
  • 6
  • 32
  • 61
  • 2
    Why do you have `.Name.Name`? You just need one name property. – SJR Aug 27 '19 at 12:16
  • 3
    @SJR `.Name` will return the `RefersTo` `.Name.Name` will return the name of the range [linky](https://stackoverflow.com/a/3630587/3042759) – Tom Aug 27 '19 at 12:20
  • 3
    @Tom - ah, I stand corrected, personally never use `RefersTo`. – SJR Aug 27 '19 at 12:21
  • if I don't use RefersToRange, then I am getting an error when defining orng and nrng – JoaMika Aug 27 '19 at 12:33
  • 1
    Well that's the road you've gone down so you can't just take out bits. Anyway there's nothing wrong with that line per se, I've just tested, so the problem lies elsewhere. – SJR Aug 27 '19 at 12:41
  • @SJR similar - have just tested the code and seems to work fine – Tom Aug 27 '19 at 12:46
  • I think the problem may be that my ranges reside inside a Listobject, and the code cannot resize them? I still get the same error.. – JoaMika Aug 27 '19 at 13:08
  • I am testing this with about 2,000 rows. It seems the macro doesn't successfully insert new rows, although there is no error on the line above. Then Excel throws an error on the Name line because there is not enough rows in the worksheet? – JoaMika Aug 27 '19 at 13:26
  • 1
    if you're using a `ListObject` what are your named ranges trying to achieve? – Tom Aug 27 '19 at 14:15

0 Answers0