I want to select cells within filtered cells.
This is my code
Sub GoToNextVisibleCellBelow()
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True
End Sub
It goes down 1 column within the column I selected. I can run this code as many times as necessary for more. However, as seen in the picture, although I could move left and right in the filtered cells, I could not select upwards.
What should I do to select an area like in the picture?
>Solution :
- Selecting the range is not necessary in most case.
How to avoid using Select in Excel VBA
- The filtered table could be non-contiguous.
- Use nested loops to iterate through areas and rows to collect the target copying range.
- Assume the table starts from cell A1.
Microsoft documentation:
Option Explicit
Sub DelTopNRows()
Dim visRng As Range, selRng As Range, iR As Long
Dim ColCnt As Long, rRow As Range, rArea As Range
Const ROWS_CNT = 4 ' modify as needed
With ActiveSheet.Range("A1").CurrentRegion
ColCnt = .Columns.Count
' get the visible range after filtering
Set visRng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
If Not visRng Is Nothing Then
' check if the cnt of visible rows > desired rows
If visRng.Cells.Count / ColCnt > ROWS_CNT Then
' loop through areas > rows
For Each rArea In visRng.Areas
For Each rRow In rArea.Rows
' collect target rows range
If selRng Is Nothing Then
Set selRng = rRow
Else
Set selRng = Application.Union(selRng, rRow)
End If
iR = iR + 1
If iR = ROWS_CNT Then Exit For
Next
If iR = ROWS_CNT Then Exit For
Next
Else
Set selRng = visRng
End If
' delete rows
If Not selRng Is Nothing Then selRng.EntireRow.Delete
End If
End With
End Sub
