CODEWITHSUNDEEP
excelvba
Leedo
Leedo

Reputation: 611

Selecting Visible Cells using SpecialCells(xlCellTypeVisible)

The idea of my code to Union cells with Interior.Color = vbYellow in addition to the cells selected manually.

Then intersect both ranges into one range from ("C:F").

The problem: there is a case that invisible cells are added to the final range.

I used SpecialCells(xlCellTypeVisible).

How to produce the issue

With the attached picture:

  1. Select range("F3:F4") V-2620 then fill color with yellow.
  2. On column C, filter on value 2620 then fill color with yellow for the visible cells from C3 till C8

The result is one range ("C3:F8"). Expected two ranges C3:F4 & C7:F8

example spreadsheet

Sub Automatic_Save_Selection()
 
   ActiveSheet.AutoFilter.ShowAllData
 
   Dim ws As Worksheet: Set ws = ActiveSheet
   Dim crg As Range
   Set crg = ws.UsedRange
   Set crg = crg.Offset(1, 0).Resize(crg.Rows.Count - 1, crg.Columns.Count)   'UsedRange except first Row
 
'____Find by Yellow Color_______________________
 
   With Application.FindFormat
     .Clear
     .Interior.Color = vbYellow
     .Locked = True
   End With
 
    Dim uRng As Range, cel As Variant, FirstAddress As Variant
    Set cel = crg.Find(What:=vbNullString, SearchFormat:=True)
 
    If Not cel Is Nothing Then
      FirstAddress = cel.Address
        Do
            If uRng Is Nothing Then
                Set uRng = cel
            Else
                Set uRng = Union(uRng, cel)
            End If
                Set cel = crg.Find(What:=vbNullString, after:=cel, SearchFormat:=True)
        Loop While cel.Address <> FirstAddress
    End If
 
'_____Union (Find by Yellow Color) with Manual Selection__________________
 
  Dim rng As Range
 
  If Not uRng Is Nothing Then
     Set rng = Union(Selection.SpecialCells(xlCellTypeVisible), uRng)
   Else
     Set rng = Selection.SpecialCells(xlCellTypeVisible)
  End If
 
  Dim TrimmedRange As Range
   Set TrimmedRange = Intersect(rng, ws.UsedRange.Offset(1))
    Intersect(TrimmedRange.EntireRow, ws.Range("C:F")).Select
 
End Sub

Upvotes: 0

Views: 924

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

All previous suppositions were not correct!

Only now I observed that your code starts with:

  ActiveSheet.AutoFilter.ShowAllData

which generates the problem, Selection.SpecialCells(xlCellTypeVisible) being all the range and that's why the returned Union Intersected range...

The good part from this story, is that I built a function able to extract in an array the filtering criteria, range and items of a existing filter and refilter the range based on the previously extracted array...

So, comment or delete the code line in discussion and use the initial scenario without any problem! :)

Upvotes: 1

Related Questions