Macro - Setting a Range

[CindyA]

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.

 

[DRJ]

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.

 

[CindyA]

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

 

[Norie]

What error?

 

[CindyA]

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.

 

[WillR]

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... ?

 

[DRJ]

Try

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?

 

[CindyA]

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,