The most urgent question for the target area is how we determine the visible cells, since we assume that the data can be filtered in any way.

Luckily, Excel has the SpecialCells function and most programmers call it directly. However, this feature comes with a few issues and may not work as expected. This is the case, for example, if we only check a single cell.

Code:


Sub Example_SpecialCells_DontWork()
  'Create the scenario
  ScenarioA
  'Clear some cells
  Range("B2:C2,D1:D2,5:10").Clear
  'B2 is empty, so this cell should be selected only!
  Range("B2").SpecialCells(xlCellTypeBlanks).Select
End Sub
To be 100% sure, we need to encapsulate the function in another function and catch this error. If you found this article by chance, the SpecialCells function is included in the files in the modLowLevel module, the download link is at the top of the article.

Code:


Sub Example_SpecialCells_Works()
  'Create the scenario
  ScenarioA
  'Clear some cells
  Range("B2:C2,D1:D2,5:10").Clear
  'B2 is empty, so this cell should be selected only!
  SpecialCells(Range("B2"), xlCellTypeBlanks).Select
End Sub
And with that we can also deal with another problem, it is possible that no cells are visible at all and in this case our function returns Nothing.

Code:


Sub Example_SpecialCells_RTE()
  Dim Dest As Range
  'Create the scenario
  ScenarioA
  'Select some cells
  Range("B3:E5").Select
  'Filter the cells
  Range("A1").AutoFilter
  Range("A1").AutoFilter 1, "A8"
  'In case of all seleczed cells are filtered out, no cells are visible!
  'This is the save way to detect this
  Set Dest = SpecialCells(Selection, xlCellTypeVisible)
  If Dest Is Nothing Then
    MsgBox "No visible cells"
  End If
  'This line results in an RTE 1004
  Set Dest = Selection.SpecialCells(xlCellTypeVisible)
End Sub
With this we have almost all the tools together, only one thing is missing and to understand why we need it we have to look at a new scenario.