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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. 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!

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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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)
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top