Checking every nth cell in named range

[scoha]

I want to check every nth cell in a named range to see if it ="a"

If it does, i want to hide that column and next two columns and then keep going.

How do I use a "For Each c in MyRange" type line but looping through every nth cell and not each cell?

 

[villy]

try to use

For each nth cell in MyRange or

for i=nth to lastrow...
try to do something like that

 

[Ruddles]

  Dim oCell As Range
  Dim iCount As Long
  
  iCount = 0
  For Each oCell In Range("MyRange")
    iCount = iCount + 1
    If iCount Mod [COLOR=blue][B]7[/B][/COLOR] = [COLOR=red][B]1[/B][/COLOR] Then
     [COLOR=green] ' do something
[/COLOR]      MsgBox iCount
    End If
  Next oCell

Set the figure in blue to the interval you want to check - here 7 means every 7th cell - and the figure in red to where you want to start, so: 1 means check cells 1, 8, 15, etc; 2 means check cells 2, 9, 16, etc; 0 means check cells 7, 14, etc.

(Tested and working here.)

 

[scoha]

Thanks Ruddles - exactly what I needed and so simple when you know how!

 

[Peter_SSs]

Is your named range just a single row?

You could just skip to the appropriate cell each time. Something like this.

Sub HideCols()
    Dim iCount As Long
    
    Application.ScreenUpdating = False
    With Range("MyRange")
        For iCount = 2 To .Columns.Count Step 3
            With .Cells(1, iCount)
                If .Value = "a" Then
                    .Resize(, 3).EntireColumn.Hidden = True
                End If
            End With
        Next iCount
    End With
    Application.ScreenUpdating = True
End Sub

Using the same idea as Ruddles, the red number indicates the first column of the named range to check and the blue number is how many columns to step across each time. So my code checks the 2nd, 5th, 8th etc columns in the range.

 

[Ruddles]

...so simple when you know how!

Well in fact I didn't know how - I had to think about it for a moment.

When faced with creating loops to process data repetitively, the general technique is to work out where you need to start and end, and what the simplest pattern is for moving from one piece of data to the next. Once you've got those clear in your mind, the writing of the code often becomes a mechanical process.

I can't tell you how many times I've started a macro by typing exactly this:-

Sub xxx()
  
  Dim ws As Worksheet
  Dim iRow As Long
  Dim iLastRow As Long
  
  Set ws = ThisWorkbook.Sheets(1)
  iLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

 

[pdowg881]

  Dim oCell As Range
  Dim iCount As Long
  
  iCount = 0
  For Each oCell In Range("MyRange")
    iCount = iCount + 1
    If iCount Mod 7 = 1 Then
      ' do something
      MsgBox iCount
    End If
  Next oCell

I'm trying to use this to highlight cells with oCell.Interior.ColorIndex = 3

Is there a way to adapt this to highlight four cells, skip four cells, and repeat?

Thanks.

 

[Ruddles]

So want you want to do is count in eights, with the first four cells of every eight highlighted and the last four cells of every eight not highlighted.

It would be something like this:-

  Dim iCount As Long
  
  For iCount = 1 To 100 Step 8
    Cells(iCount, "A").Resize(4, 1).Interior.ColorIndex = 3
  Next iCount

Of course you have to work out where to start and end instead of using 1 and 100, but if you try that code on a blank worksheet you'll see how it works.

Any probs, give me a shout.