Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi,
    My macro below finds the words "Eligibility Report", which are located in cell A189. I want x to be defined as A189 so the range that can be selected is as follows... Range("A189:Z65536").Select
    Then I want to clear the contents of this range. My x isn't working, so I've clearly got it written wrong. Any help is appreciated.
    Thanks!
    Lana



    Sub FindAndClear()

    Dim x As Long

    x = Cells.Find(What:="Eligibility Report").Select

    Range(x & ":" & "Z65536").Select

    Selection.ClearContents

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Code:
    Dim oCell As Range
    Set oCell = Cells.Find(What:="Eligibility Report")
    If Not oCell Is Nothing Then
      Range(oCell, Range("Z65536")).ClearContents
    End If
    Note that the code doesn't select anything. In general, it's not necessary to select a range in order to do something with it in code, and it runs more efficiently if you don't select.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    This worked perfect... thanks so much Hans. This code will come in handy for not just this project, but I can think of several others that I can use this for as well.
    Thanks again!!!
    Lana

Posting Permissions

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