Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range Specification (2000)

    I have a spreadsheet that allows an entry in cells B4:J12. Less than half of these cells will contain numeric data, and the order is random as to which cells will have numbers inputed. All the other cells will remain blank.

    What I am trying to do is use the Range().Select function in VBA to select only the range of cells with values.

    I can't seem to come up with any creative ways of doing this. I want to manipulate the formatting of the range with numeric values without affecting the blank cells.

    Any thoughts??

    Chris

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    You mean other than applying conditional formatting?

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    I cannot use conditional formatting in this case. After the user has entered their numeric values in some of the cells, I want them to click a button to run the macro that will create the range of those cells that have numeric values. I will need to know this range for later use.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range Specification (2000)

    Appropriate variations on

    <range>.SpecialCells(xlCellTypeConstants, xlErrors + xlLogical + xlNumbers + xlTextValues).Select

    will select these for you, and from there you can feed them to a range. Post back if you need more specific help.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    When you wrote the following in your original message, I assumed you were only concerned with formatting.

    I can't seem to come up with any creative ways of doing this. I want to manipulate the formatting of the range with numeric values without affecting the blank cells.


    You are no longer concerned with formatting? You want the range (defined as Min and Max) of the cells within the specified area?

    =MIN(B4:J12) and =MAX(B4:J12) won't work?

    Called from within code, the range B4:J12 could be set to some constant, and you could use

    WorksheetFunction.Max() and WorksheetFunction.Min()

    Is this what you are after?

  6. #6
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    Thanks John. What you provided was a little foreign to me, but it got me going in the right direction. After some help file use and searching on the keyword 'SpecialCells' that you provided I was able to get what I wanted. The results are:

    SELECTS THOSE CELLS IN MY RANGE THAT HAVE NUMERIC VALUES:
    Range("B4:J12").SpecialCells(xlCellTypeConstants, xlNumbers).Select

    SELECTS THE BLANK CELLS ONLY
    Range("B4:J12").SpecialCells(xlCellTypeBlanks).Sel ect

    Thanks for the help everyone! I apologize I was not very clear with what I needed.

  7. #7
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    No wonder why it was so foreign. John, I never looked at your actual post on WOPR. I only read the email I received. For some reason my email made the code you provide look very foreign adding all kinds of weird characters and symbols. What you actually provided in your post was exactly what I needed! HA!

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range Specification (2000)

    Oh, yeah, the '<' and '>' signs get munged if you get plain text messages, or something like that. (Read this on the Lounge, not from email! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>)
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    One more question... If I assign a String variable to equal my range, how can I access this same range later?

    Dim BlankOnes as String
    BlankOnes = Range("B4:J12").SpecialCells(xlCellTypeBlanks).Sel ect

    After the blank cells are no longer blank, I want to be able to refer back to this same range when they were blank so I can clear only those cells. Ideally, I want something like below, but can't figure out the proper code.

    Range(BlankOnes).Selection
    Selection.ClearContents

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Range Specification (2000)

    Dim BlankOnes as range
    set BlankOnes = Range("B4:J12").SpecialCells(xlCellTypeBlanks)
    BlankOnes.ClearContents

    Steve

  11. #11
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    Perfect... Thanks!

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range Specification (2000)

    Note that Steve has declared the range as a Range object rather than a String, and his is the better way to declare the variable. Declaring it as a Range has other advantages for VBA processing, such as you can loop through each cell in the range (air code):

    For each myCell in myRange.Cells
    do something
    Next myCell

    (What took you so long, Steve?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    Still learning VBA... Didn't know I could even declare a Range until now... Thanks guys!

  14. #14
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range Specification (2000)

    Would you happen to know if you can force Excel to full-screen view for the end user by using VBA code with Workbook_Open()? And then back to regular view upon Workbook_BeforeClose?

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Range Specification (2000)

    Yes, syntax below, but the user can change it unless you lock the workbook accordingly.

    Application.DisplayFullScreen = True
    Application.DisplayFullScreen = False

    You can often get syntax like this by running the Macro Recorder, which how I got the above. However, the Macro Recorder records very inefficient code and does not expose the power of VBA (such as loops, declared Ranges, etc.) - use it only to get syntax.

    Here's a related tip: I refreshed my memory on the syntax for the SpecialCells Method by recording a quick Macro using Edit | Goto | Special | Constants, then checking the SpecialCells Method VBA Help for the parameter names - using parameter names helps the code be more self documenting. Try it and see.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •