选择过滤列标题正下方的第一个可见单元格 [英] Select first visible cell directly beneath the header of a filtered column

问题描述

我试图选择过滤列标题正下方的第一个可见单元格.我得到的代码如下，但是我必须对此代码有问题.首先，第一行代码正在使用文件的当前活动范围.此文件很有可能会更改，并且此范围将不同.如何使它适用于我将在其上使用的任何文件?其次，如果我使用完全相同的列格式的完全不同的文件，则列J下的第一个可见单元格可能是J210.如何使此方法适用于任何变量数组?

I am trying to select the first visible cell directly beneath the header of a filtered column. The code I am getting is as below, but I have to problems with this code. First, the first line of code is using the current active range of the file. It is highly likely that this file will change and this range will not be the same. How can I make it work for any file I would use it on? Second, if I use a totally different file with the same column format, the first visible cell under Column J could be J210. How can I make this work for any array of variables?

Sub Macro16()
'
' Macro16 Macro
'

'
    ActiveSheet.Range("$A$1:$R$58418").AutoFilter Field:=12, Criteria1:= _
        "Sheets"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],3)"
    Selection.FillDown
End Sub

推荐答案

未经测试，但:

Sub Macro16()

    With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=12, Criteria1:="Sheets"
        If .Columns(1).SpecialCells(xlCellTypeVisible).count > 1 Then
            With .Columns(10)
                .Resize(.rows.count - 1).offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RIGHT(RC[1],3)"
            End With
        End If
    End With

End Sub

这篇关于选择过滤列标题正下方的第一个可见单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！