none
How big is my Range? RRS feed

  • Question

  • Hi All.........WinXP, XL2010 here

    I'm using the following code to select a range of interest.

    I would like to test that range to see if it contains more than 16 cells, (or 8 rows, whichever is easier) before deciding to copy it, or issue a warning message that it is oversize.

    TIA for any ideas,

    Chuck, CABGx3

    Dim findcell
            Sheets("WorkOrders").Select
            Sheets("reports").Select
            Range("D65000").Select
            Selection.End(xlUp).Select
    findcell = Selection.Address
       
            Range("c12:" & findcell).Activate
            Selection.Copy


    Chuck, CABGx3

    Monday, June 10, 2013 1:22 PM

Answers

  • Chuck,

    Try it this way (no selecting needed):

    Dim rngFindCells As Range

    With Sheets("reports")
        Set rngFindCells = .Range(.Range("C12"), .Cells(.Rows.Count, "D").End(xlUp))
    End With

    MsgBox "The range is " & rngFindCells.Cells.Count & " cells."

    If rngFindCells.Cells.Count > 16 Then
        MsgBox "That range is too big."
    Else
        rngFindCells.Copy
    End If

    • Marked as answer by CABGx3 Tuesday, June 11, 2013 3:08 AM
    Monday, June 10, 2013 2:12 PM

All replies

  • Chuck,

    Try it this way (no selecting needed):

    Dim rngFindCells As Range

    With Sheets("reports")
        Set rngFindCells = .Range(.Range("C12"), .Cells(.Rows.Count, "D").End(xlUp))
    End With

    MsgBox "The range is " & rngFindCells.Cells.Count & " cells."

    If rngFindCells.Cells.Count > 16 Then
        MsgBox "That range is too big."
    Else
        rngFindCells.Copy
    End If

    • Marked as answer by CABGx3 Tuesday, June 11, 2013 3:08 AM
    Monday, June 10, 2013 2:12 PM
  • Only a perfect solution.........thanks Bernie, much appreciate.

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Tuesday, June 11, 2013 3:09 AM