Basic Variable Problem

[hdbkiwi]

Hi Everyone,

First off, a huge thanks to everyone helping out here - I love this community and have learned so much from reading other threads.

I'm trying to do a very simple for loop that cycles through sheets and executes a long code within that sheet. However, for some reason, the for loop is not working and the code only executes on the first sheet. My code is below:

Sub RowDel()


x = Worksheets.Count
For J = x To 2 Step -1
    Application.DisplayAlerts = False
    
With Sheets(J) 'example code to execute
Rows("1:1").Delete Shift:=xlUp
Rows("3:3").Delete Shift:=xlUp
End With


Next J
Application.DisplayAlerts = True
End Sub

Any ideas?

 

[RoryA]

Your With block doesn't actually do anything currently as you haven't qualified the Rows property calls:

With Sheets(J) 'example code to execute
.Rows("1:1").Delete Shift:=xlUp
.Rows("3:3").Delete Shift:=xlUp
End With

is what you need.

 

[hdbkiwi]

Oh goodness, so basic, thank you very much! Can't believe I missed that.

It's gone through and is now deleting the first rows which is amazing.

I thought what I would do next is delete out rows that are of a certain colour, but in order to do that I need to reset the used range in each worksheet.

I then go on in my code to write:



Dim rng As Range
Set rng = Sheets(J).UsedRange
rng.Select




RowCount = rng.Rows.Count
For i = RowCount To 2 Step -1
If Cells(i, 2).Interior.ColorIndex = 14 Or Cells(i, 2).Interior.ColorIndex = 43 Then Cells(i, 1).EntireRow.Delete
Next i

--

However, it stops at "rng.Select" with a Run-time error of '1004' Select method of Range class failed.

Any ideas?

 

[RoryA]

You cannot select a range on a sheet without activating that sheet first. However, there is no need to select the range so simply remove that line. And note that you have not qualified your Cells calls with a worksheet...