Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
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!
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!
Code:
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