Deleting a range in all worksheet - what am i doing wrong?

[ParanoidAndroid]

Hi Guys

I would be very thankful if you could tell me what is wrong with the following mothod.

I simply want to delete a range in all worksheets except for the one titled Monkey.

The range will always start at A2 and i want it to delete until the last cell in column J.

It seems to be correct in deleting as far as the last row but not the last column


Dim ws As Worksheet<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim Last_cell_in_Range As Range<o></o>

For Each ws In ActiveWorkbook.Worksheets
If ws.name <> “Monkey” then
Range("J2", Range("A65536").End(xlUp)).Name = "Last_cell_in_Range"<o></o>
Range("A2:Last_cell_in_Range").Delete<o></o>
<o> </o>
Endif
next

 

[jeffreybrown]

You need to qualify the range with ws...

an example...untested

Sub AllWS()
    Dim ws As Worksheet
    Dim Last_cell_in_Range As Range
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> “Monkey” Then
            ws.Range("J2", Range("A65536").End(xlUp)).Name = "Last_cell_in_Range"
            ws.Range("A2:Last_cell_in_Range").Delete
        End If
    Next ws
End Sub

 

[Leith Ross]

Hello ParanoidAndroid,

Here is another way to do it...

  Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
      If ws.Name <> “Monkey” Then
         ws.Range("J2", ws.Cells(Rows.Count, "J").End(xlUp)).Delete
      End If
    Next ws

Sincerely,
Leith Ross

 

[Dryver14]

just had a play with this and if you have any info in K it moves to A

are you sure you dont want to just clearcontents?

 

[ParanoidAndroid]

Hi Dryver

I want to clear all contents except that in the first row for each sheet except for one of the sheets,

The contents in columns L and M are to remain in L and M. I dont wish for it to be shifted to columns A.

By All contents...this would be A2 to Column J (not affecting row 1)

This what your asking?

 

[Dryver14]

yes what I am saying is if you want the worksheet to stay the same then use .clearcontents instead of .delete