Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Used.Range Tweak (2003 SP2)

    Earlier help on forcing the format of cells to plain text gave this piece of VBA. It works so well that I'd like to tweak it to work on, say, a selection of cells of the "A10:B15" or named range "NeedChange" description. My attempts so far have caused the code to change the formatting of cells outside the selection I wanted. My guess is that there is some property of Used.Range that I'm not familiar with. Any suggestions?


    Application.ScreenUpdating = False
    Rows("11:9999").Select

    With ActiveSheet.UsedRange
    .Value = .Value
    End With
    Application.ScreenUpdating = True

    End Sub

    Many thanks,

    Ann

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Used.Range Tweak (2003 SP2)

    Just change this:
    With ActiveSheet.UsedRange
    .Value = .Value
    End With

    To:

    With ActiveSheet.Range("NeedChange")
    .Value = .Value
    End With

    Or:

    With ActiveSheet.Range("A10:B15")
    .Value = .Value
    End With
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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