Expand Range In Macro

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I have a named range. The range includes a block of cells plus one extra row and one extra column. That is so I can insert a new row or a new column and still have them within the range. The last row and column are simply blank.

    I am trying to figure out how to select the part of the range that excludes the extra row and column, so I can then do a fill-right and fill-left. It seems like this should not be hard but I am getting all gummed up in the syntax.

    The last line of the following code excerpt produces a run-time error 13 Type mismatch. It compiles, however. (I have omitted some extraneous lines that have no effect on r or rf.)

    Dim r As Range
    Dim rf As Range
    Set rf = Range("TotalFTEByStaff")
    Set r = Range(rf.Cells(1, 1), rf.Cells(rf.Rows.Count - 1, rf.Columns - 1))
  • Re: Syntax For A Range Within A Range?

    I think you just missed '.Count' after 'Columns'

  • Re: Syntax For A Range Within A Range?

    I prefer norie's suggestion to use Resize, but if you just want to fix your code, add ".Count" after Columns.

  • Re: Syntax For A Range Within A Range?

    Thanks! The missing .count certainly was a problem but Resize works like a charm. I didn't know about that one.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!