Expand Range In Macro

  • 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!