Trying to create a macro that uses a formula on only visible cells.

[DrFistington]

I have a macro that I'm working on, and for the most part the macro works well except for one issue. When the source data is exported, it will be over the course of several months, and the end user will filter out all of the months except the one they want to look at. I need to have the macro calculate only for the visible, non-filtered items. Here is the code for my macro (many of the lines are commented out since I had used them at some point in testing, but currently don't need them:

 Dim TheAnswer$
    
    TheAnswer = ActiveSheet.AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible).Rows.Range
   
    'ActiveSheet.Cells.SpecialCells(xlCellTypeVisible, xlLastCell).Row
    'ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Areas.Count
    'ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Row
    
    'ActiveSheet.Cells.SpecialCells(xlCellTypeVisible, xlLastCell).Row
    
    'TheAnswer = InputBox("Enter the number of surveys that were submitted!")
    
     ' Select column you want value to appear in, beginning with cell that has data
     ' This will go down until an empty cell is found
    'Range("A1").Select
    'Do Until ActiveCell.Value = ""
       ' ActiveCell.Offset(1, 0).Range("A1").Select
    'Loop
     ' Or you can use this to select the very last bottom-right cell that's been used
     ' and then select the next cell down
     ' ActiveCell.SpecialCells(xlLastCell).Select
     
    Range("A" & TheAnswer + 4).Select
    ActiveCell.Value = "% of yes responses"
    
    Range("F" & TheAnswer + 4).Select
    Selection.NumberFormat = "0.00%"
  
ActiveCell.Formula = "=COUNTIF(F2:F" & TheAnswer & " ,""Yes"")/COUNTA(F2:F" & TheAnswer & " )"

 Range("G" & TheAnswer + 3).Select
 Selection.NumberFormat = "0.00%"
ActiveCell.Formula = "=COUNTIF(G2:G" & TheAnswer - 1 & " ,""Yes"")/COUNTA(G2:G" & TheAnswer - 1 & " )"

The main issue is that when determining the percentage of a response, the denomenator is calculated based on ALL values, not just the visible ones. For example, if the spreadsheet has 9 entries, 1 row for labels, 7 rows for May, and 1 row for april, and I filter out april, when the % of yes responses for may is calculated by the macro its going to be x/8, when really it should be x/7 since the row containing the month of April is filtered out.