VBA Help - How to update Range based on Filtered Criteria - Excel 2016

[Johnny Thunder]

Hello All,

I have come to a spot in this code that I am not sure how to tackle. I will do my best to explain what the problem is,

I have one code that is able to be triggered from 3 different sheets, the outcome for all three sheets is the same, the only thing that varies is the data that the code grabs. So, for this I wrote a quick piece of code that looks at a sheet "Master Data Entry" and filters based on criteria, the problem that I am having is once the data is filtered I have a For Each piece of code that retrieves the filtered data but the Loop is grabbing cells that are hidden by the filter because my range for the loop is the start of the data to the last row, not the actual filtered rows.

How can I adjust this piece of code to suit? I pasted and commented up the line that needs the revision so if anyones got any ideas please let me know. Thanks!

Sub BuildTitles()


Dim ws1                 As Worksheet, ws2 As Worksheet
Dim r                      As Long, c As Long, LastR2 As Long
Dim Title                As String, Season As String, AvailTime As String, Commitment As String, Genre As String, LastChar As String
Dim TitleLength     As Long, SeasonLength As String
Dim BlockVariable  As Variant, ShtName As Variant
   
ShtName = ActiveSheet.Name
  
   Set ws1 = Sheets(ShtName)
   Set ws2 = Sheets("Master Data Entry")
   LastR2 = ws2.Range("E" & Rows.Count).End(xlUp).Row
   
'----------------------------- Filter Mode------------------------------------
On Error Resume Next
ws2.ShowAllData 'Clear Filter
Select Case ShtName
 
    Case "Drama & Comedy"
            ws2.Range("$B$1:$N$" & LastR2).AutoFilter Field:=5, Criteria1:="=Comedy", Operator:=xlOr, Criteria2:="=Drama"
                
    Case "Unscripted, Film, Kids"
            ws2.Range("$B$1:$O$" & LastR2 & "").AutoFilter Field:=5, Criteria1:=Array("Film", "Kids", "Uncripted"), Operator:=xlFilterValues
           
    Case "Oprah"
            ws2.Range("$B$1:$N$" & LastR2).AutoFilter Field:=5, Criteria1:="=Oprah"
                       
End Select
'----------------------------- Filter Mode------------------------------------


   r = 7 'Start at row 7
   c = 10 'Tile Start at Column 10 = J
   
   ws1.Range("J7:L33").ClearContents 'Clear tiles at the start - This is for the sorting
   
   For Each Cell In ws2.Range("B2:B" & LastR2 & "")  '<---------------My Loop that looks at all lines instead of just the filtered data

 

[BobUmlas]

Change
For Each Cell in ws2.Range("B2:B" & LastR2) 'you don't need the last & ""s
to
For Each Cell in ws2Range("B2:B" & LastR2).Specialcells(xlcellTypeVisible)