Adding to the 20th visible row

[DrH100]

Hi all
I’m looking to be able to either amend the code below orhave a new code that loops through the range and identifies every 20th visible row (after the header) and adds “yes” to column “S”.

I've been able to find an alter code that identifies andselects the first 20 rows but I’ve got to be honest and say I haven’t got aclue where to start getting it to loop through the range of data (or really what the original code does but I know it works).

Public Sub AllChanges_filter()


Application.ScreenUpdating = False


Sheets("AllChanges").Select


Dim lngStart As Long, lngEnd As Long


lngStart =Sheets("Sheet2").Range("a5").Value 


lngEnd =Sheets("Sheet2").Range("b13").Value 


ActiveSheet.Range("$A$1:$Q$364").AutoFilterfield:=18, Criteria1:="Y"


ActiveSheet.Range("$A$1:$Q$364").AutoFilterfield:=17, Criteria1:= "Post Approval"


ActiveSheet.Range("$A$1:$Q$364").AutoFilterfield:=16, Criteria1:="=0", Operator:=xlOr, Criteria2:="=I"


ActiveSheet.Range("$A$1:$aa$364").AutoFilterfield:=14, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<="& lngEnd


Columns("p:p").Select


Selection.EntireColumn.Hidden = True


Range("A1").Select


Sheets("SnapShot").Select


Application.ScreenUpdating = True


End Sub

Any ideas or help appreciated

 

[Fluff]

How about

Sub AddYes()
   Dim Rng As Range
   Dim i As Long
   For Each Rng In Range("S2", Range("S" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
      i = i + 1
      If i Mod 20 = 0 Then Rng.Value = "YES"
   Next Rng
End Sub

 

[Rick Rothstein]

How about

Sub AddYes()
   Dim Rng As Range
   Dim i As Long
   For Each Rng In Range("S2", Range("S" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
      i = i + 1
      If i Mod 20 = 0 Then Rng.Value = "YES"
   Next Rng
End Sub

I wanted to see if eliminating the loop might speed things up, but it didn't as the following code (much longer than yours) runs in exactly the same amount of time as your code does (tested for 1000 rows with a random number of hidden rows... 0.02 seconds each)...

[table="width: 500"]
[tr]
	[td]Sub AddYes2()
  Dim LastRow As Long, UnusedCol As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  UnusedCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious, , , False).Column + 1
  With Range(Cells(2, UnusedCol), Cells(LastRow, UnusedCol))
    .Formula = "=SUBTOTAL(103,A$2:A2)"
    .Value = Evaluate("IF(MOD(" & .Address & ",20)=0,""Yes"","""")")
    Intersect(Columns("S"), .SpecialCells(xlConstants).EntireRow).Value = "YES"
    .Clear
  End With
End Sub[/td]
[/tr]
[/table]

 

[MARK858]

Hi Rick, just a quick question...
Are you pasting your code differently lately as it has been losing it's formatting when I look at it (it actually has table tags when looking at it in reply with quote)?

 

[Fluff]

I get a YES on all visible rows.
It outputs the "row number" in correctly, but the evaluate then turns them all into "yes"

 

[Rick Rothstein]

Hi Rick, just a quick question...
Are you pasting your code differently lately as it has been losing it's formatting when I look at it (it actually has table tags when looking at it in reply with quote)?

Check it again... when I first posted this, I had edited something and accidentally removed the "[/" from the beginning of the "[/CODE]" tag. When I replaced those two characters, the listing "straightened out"... so I'm thinking you saw it in between my original posting and my edited posting.

I get a YES on all visible rows.
It outputs the "row number" in correctly, but the evaluate then turns them all into "yes"

I assumed there were values in Column A to guide the counting processes. If you don't have values in that column, then I think you will get the output you saw... with values in Column A, I get the same output as your code generates.

 

[MARK858]

Check it again... when I first posted this, I had edited something and accidentally removed the "[/" from the beginning of the "[/CODE]" tag. When I replaced those two characters, the listing "straightened out"... so I'm thinking you saw it in between my original posting and my edited posting.
.

Still the same I am afraid and I have noticed it with a few of your recent posts which I have never had any issues with the way your code displays previously. See below...



https://imgur.com/Ka83M9N

 

[Fluff]

Must be something odd about my data.
If A2 is hidden, all rows visible rows get Yes, but if A2 is visible only the 20th row gets yes

 

[Fluff]

@MARK858
Rick's code looks normal to me & nothing like the image you've posted, regardless of which display mode I use.

 

[MARK858]

@MARK858
Rick's code looks normal to me & nothing like the image you've posted, regardless of which display mode I use.

A bit strange it must be an update with IE as it looks better in Chrome, just weird that it isn't affecting other posts.

Image from Chrome...