# VBA .clear every nth range



## Young Grasshopper (Dec 15, 2022)

Hi World, 

I started this code: 

```
ThisWorkbook.Worksheets("Sheet1").Range("E5:F510,J5:K510,O5:P510,T5:U510,Y5:Z510").Clear
```
So this is two columns, then skip three columns and so on. I'm going to SG510 (100 repetitions)... Quickly found out that this is not efficient..
What would be the quicker code to use?
Row 4 and up needs to stay untuched, but the range don't need to stop at 510 if its easier to just clear the rest of the cell. 

Would appreciate any help


----------



## Joe4 (Dec 15, 2022)

Here is one way:

```
Sub MyClearRanges()

    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Loop through 100 times
    For c = 1 To 100
'       Clear range
        ThisWorkbook.Worksheets("Sheet1").Range(Cells(5, c * 5), Cells(510, c * 5 + 1)).Clear
    Next c
    
    Application.ScreenUpdating = True
    
End Sub
```


----------



## offthelip (Dec 15, 2022)

I would do it like this:

```
Sub test()
'ThisWorkbook.Worksheets("Sheet1").Range("E5:F510,J5:K510,O5:P510,T5:U510,Y5:Z510").Clear
For i = 1 To 100
 ThisWorkbook.Worksheets("Sheet1").Range(Cells(5, i * 5), Cells(510, 1 + i * 5)).Clear
Next i

End Sub
```


----------



## Young Grasshopper (Dec 16, 2022)

Both solutions works great, but one thing i didn't mention (because i didn't realise it before now..) is that it have to work even if I'm in another sheet. 
But since the code needs to loop it's way trough the sheet, would that be impossible?


----------



## Joe4 (Dec 16, 2022)

Young Grasshopper said:


> Both solutions works great, but one thing i didn't mention (because i didn't realise it before now..) is that it have to work even if I'm in another sheet.
> But since the code needs to loop it's way trough the sheet, would that be impossible?


Are you saying that you want it to work on ANY sheet?
If so, that is a simply thing.  Just remove the Sheet reference in the code, and it will default to the ActiveSheet, i.e.

```
Sub MyClearRanges()

    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Loop through 100 times
    For c = 1 To 100
'       Clear range
        Range(Cells(5, c * 5), Cells(510, c * 5 + 1)).Clear
    Next c
    
    Application.ScreenUpdating = True
    
End Sub
```
Just note, that the VBA code will have to be placed in a General Module, and not one of the specific Sheet modules in VBA.


----------



## Young Grasshopper (Dec 16, 2022)

Sorry no, I'm saying I want it to work FROM any Sheets. So i can be in Sheet 2, or 3 etc and run the code on Sheet 2. If this was an easy Range().clear code, that wouldn't be a problem, but since this code loops trough the columns, maybe it can't work like that?


----------



## RoryA (Dec 16, 2022)

It will work fine, you just need to specify the sheet for the Range and Cells calls:


```
Sub MyClearRanges()

    Dim c As Long
    
    Application.ScreenUpdating = False
    
'   Loop through 100 times
    For c = 1 To 100
'       Clear range
        With ThisWorkbook.Worksheets("Sheet1")
            .Range(.Cells(5, c * 5), .Cells(510, c * 5 + 1)).Clear
        End With
    Next c
    
    Application.ScreenUpdating = True
    
End Sub
```


----------



## RoryA (Dec 16, 2022)

FWIW, I'd prefer to keep it to one clear operation using something like this:


```
Sub ClearCols()
   With ThisWorkbook.Worksheets("Sheet1")
      Dim clearRange As Range
      Set clearRange = .Range("E5:F510")
      Dim rowCount As Long
      rowCount = clearRange.Rows.Count
      Dim colNum As Long
      For colNum = .Range("J5").Column To .Range("SG5").Column Step 5
         Set clearRange = Union(clearRange, .Cells(5, colNum).Resize(rowCount, 2))
      Next colNum
   End With
   clearRange.Clear
End Sub
```


----------



## Young Grasshopper (Dec 16, 2022)

Don't know, range and sheet seems to be correct.. But your code, Rory, worked like a dream and was superfast, so could be that I did something wrong with the other codes. 
Anyway, I got what I need, so thank you all


----------



## RoryA (Dec 16, 2022)

Young Grasshopper said:


> Don't know, range and sheet seems to be correct


This bit:

```
ThisWorkbook.Worksheets("Sheet1").Range(Cells(5, i * 5), Cells(510, 1 + i * 5)).Clear
```
only specifies the sheet for the `Range` property - it needs to be done for the `Cells` properties too.


----------

