Results 1 to 8 of 8

Macro - Setting a Range

This is a discussion on Macro - Setting a Range within the Excel Questions forums, part of the Question Forums category; I have a macro containing the following line: Set Rng = Columns(1) I understand the that the #1 after Columns ...

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    102

    Default Macro - Setting a Range

    I have a macro containing the following line:
    Set Rng = Columns(1)

    I understand the that the #1 after Columns indicates column A. I want, however to have the macro work with a range of columns from A through M. I have tried Columns(1:10), but it doesn't like it. Any help would be appreciated.

    Thanks in advance.

  2. #2
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Set Rng = Range("A1:M65536")

    btw if you are going to be using a loop, you may want to reconsider working on the whole column.

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    102

    Default Re: Macro - Setting a Range

    I changed the line to
    Set Rng = Range("A1:M50")

    and now it produces an error below (the green area in the code). Sorry I'm such a rookie at this! Thanks, in advance for the help.


    Sub test()
    Dim Rng As Range, lastArea As Range, cell As Range, c&
    'Set Rng = Columns(1)
    Set Rng = Range("A1:M50")
    Set lastArea = Rng.Areas(Rng.Areas.Count)
    c = lastArea.Cells.Count
    Do
    Set cell = Rng.Find(What:="APPL", _
    After:=lastArea.Cells(c), _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False)
    If Not cell Is Nothing
    Then cell.Resize(6).EntireRow.Delete
    Loop While Not cell Is Nothing
    End Sub

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,659

    Default

    What error?
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    May 2002
    Posts
    102

    Default Re: Macro - Setting a Range

    Sorry ... I get RUN TIME ERROR 13 TYPE MISMATCH.

    When I click DEBUG, it highlights the 'green' lines as indicated in prior message.
    Thank you.

  6. #6
    Board Regular WillR's Avatar
    Join Date
    Feb 2002
    Location
    Sutton Coldfield
    Posts
    1,143

    Default Re: Macro - Setting a Range

    Might be best if we get back to basics with this one... Looks like there are a number of error in your code.. but rather than poking around in the dark, it's propbably best to try to determine what exactly you are trying to achieve with this macro... ?
    /**\ Regards, Will /**\

  7. #7
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Try

    Code:
    With Rng
        Set cell = .Find(What:="APPL", _ 
    After:=lastArea.Cells(c), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    
    If Cell is nothing then...
    Also are you sure you want to search in the formulas and not the values?

  8. #8
    Board Regular
    Join Date
    May 2002
    Posts
    102

    Default Re: Macro - Setting a Range

    Here's the entire code I have now:

    Sub test()
    Dim Rng As Range, lastArea As Range, cell As Range, c&
    Set Rng = Columns(1) 'Change as necessary
    Set lastArea = Rng.Areas(Rng.Areas.Count)
    c = lastArea.Cells.Count
    Do
    Set cell = Rng.Find(What:="APPL", _
    After:=lastArea.Cells(c), _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False)
    If Not cell Is Nothing Then cell.Resize(6).EntireRow.Delete
    Loop While Not cell Is Nothing
    End Sub

    I need to search about 10 columns of information and an undertermined amound of rows (approx. 4-500?) to find APPL. APPL can be in various columns. When it finds APPL, I need to delete the next 6 rows and continue on until no more APPL are found. The macro, above, works fine if all the APPL are in collumn A. However, the APPL may be in a variety of columns. I don't know how to tell the macro to search the entire range for APPL.

    Thanks,

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com