none
Problem with VBA resize? RRS feed

  • Question

  • I have been Wrestling with this all night. I want to resize a named range as I course through the data and validate entries. I swear I've used this same code before, but it is throwing an error I haven't been able to figure out.

    Here is a test piece I put together to experiment with.

    ub tstRngExtend()
    
    Dim idTest As Range
    Dim rCount As Integer
    
    'TESTS
    
    'ActiveSheet.Range("idTest").Select
    '
    
    rCount = ActiveSheet.Range("idTest").Rows.Count
    ActiveSheet.Range("idTest").Select
    MsgBox rCount
    
    ActiveSheet.Range("idTest").Resize(RowSize:=1, ColumnSize:=0).Select
    rCount = ActiveSheet.Range("idTest").Rows.Count
    MsgBox rCount
    
    
    End Sub
    

    When I get to the "Resize" line I get and error that says:

    "Runtime error '1004'

    Application-defined or Object-defined error

    I have tried every combination of resize that I know and I guess I'm just getting more confused.

    Can anyone spot the problem?

    Thank you,

    Mike

    Thursday, April 7, 2016 10:50 AM

Answers

  • You mixed up some things, we have to fiddle them into details.

    a) You should not use SELECT or SELCTION, that is slow and error prone. A better way is to refer to a RANGE object directly.

    b) You don't have to use named arguments on RESIZE, that makes only sense if you skip an argument on function/properties with a lot of arguments.

    c) RANGE without a prefix refers always to the active sheet when you call it from a regular module.

    So this line
      ActiveSheet.Range("idTest").Resize(RowSize:=1, ColumnSize:=0).Select
    becomes
      Range("idTest").Resize(RowSize:=1, ColumnSize:=0).Select
    becomes (where R is a variable of type Range)
      Set R = Range("idTest").Resize(RowSize:=1, ColumnSize:=0)
    becomes
      Set R = Range("idTest").Resize(1, 0)
    which throws the same error. Why?

    Please ask yourself: How many cells are in 1 row and 0 columns?
    Yeah, that's right: 0 cells! That's the reason for the error.

    So we can resize the RANGE object to one row
      Set R = Range("idTest").Resize(1)
    or one column
      Set R = Range("idTest").Resize(, 1)
    or both
      Set R = Range("idTest").Resize(1, 1)

    The last RANGE object has only one cell, for the other both lines above it is not clear, because that depends on how many rows/columns the named range "idTest" has.

    Okay, let us try something:

    Sub Test()
      Dim R As Range
      Set R = Range("idTest").Resize(1, 1)
      MsgBox Range("idTest").Rows.Count
    End Sub

    Hey, what's that? Why does the MsgBox shows a number > 1?

    When I look roughly over your lines, I would say you want to resize the named range... which is not possible that way.

    The Resize property of the Range object returns another Range object of that size, so that works in your sense:

    Sub Test()
      Dim R As Range
      Set R = Range("idTest").Resize(1, 1)
      MsgBox R.Rows.Count
    End Sub



    All right?

    Andreas.
    Thursday, April 7, 2016 3:23 PM

All replies

  • Yes, there is a "S" at the "Sub" and I didn't catch it on my copy. Sorry.

    Mike

    Thursday, April 7, 2016 10:52 AM
  • Yes, there is a "S" at the "Sub" and I didn't catch it on my copy. Sorry.

    Mike

    Thursday, April 7, 2016 10:52 AM
  • Re: resize

    There is no range with 0 (zero) columns.
    Omit the comma to only change the number of rows...
       ActiveSheet.Range("idTest").Resize(RowSize:=1)

    The comma is required if only changing the number of columns...
       ActiveSheet.Range("idTest").Resize(, 2)
       -or-
       ActiveSheet.Range("idTest").Resize( ColumnSize:=2)

    For a happier life: enter both row size and column size.

    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 4:25 AM
    Thursday, April 7, 2016 3:02 PM
  • You mixed up some things, we have to fiddle them into details.

    a) You should not use SELECT or SELCTION, that is slow and error prone. A better way is to refer to a RANGE object directly.

    b) You don't have to use named arguments on RESIZE, that makes only sense if you skip an argument on function/properties with a lot of arguments.

    c) RANGE without a prefix refers always to the active sheet when you call it from a regular module.

    So this line
      ActiveSheet.Range("idTest").Resize(RowSize:=1, ColumnSize:=0).Select
    becomes
      Range("idTest").Resize(RowSize:=1, ColumnSize:=0).Select
    becomes (where R is a variable of type Range)
      Set R = Range("idTest").Resize(RowSize:=1, ColumnSize:=0)
    becomes
      Set R = Range("idTest").Resize(1, 0)
    which throws the same error. Why?

    Please ask yourself: How many cells are in 1 row and 0 columns?
    Yeah, that's right: 0 cells! That's the reason for the error.

    So we can resize the RANGE object to one row
      Set R = Range("idTest").Resize(1)
    or one column
      Set R = Range("idTest").Resize(, 1)
    or both
      Set R = Range("idTest").Resize(1, 1)

    The last RANGE object has only one cell, for the other both lines above it is not clear, because that depends on how many rows/columns the named range "idTest" has.

    Okay, let us try something:

    Sub Test()
      Dim R As Range
      Set R = Range("idTest").Resize(1, 1)
      MsgBox Range("idTest").Rows.Count
    End Sub

    Hey, what's that? Why does the MsgBox shows a number > 1?

    When I look roughly over your lines, I would say you want to resize the named range... which is not possible that way.

    The Resize property of the Range object returns another Range object of that size, so that works in your sense:

    Sub Test()
      Dim R As Range
      Set R = Range("idTest").Resize(1, 1)
      MsgBox R.Rows.Count
    End Sub



    All right?

    Andreas.
    Thursday, April 7, 2016 3:23 PM
  • Hi Mike,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Friday, April 8, 2016 2:17 AM
  • Hi, meburkeRoboguy

    >> you have error at this place “ ColumnSize:=0”

    It cannot be zero. So if you change the value of Columnsize then your error can be solved.

    >>Andreas Killer has already gives you a proper suggestion regarding this issue.

    >> you can follow his suggestion to solve your issue

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 8, 2016 5:37 AM
    Moderator