Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

vba - Select first visible cell directly beneath the header of a filtered column

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
Sub FirstVisibleCell()
    With Worksheets("You Sheet Name").AutoFilter.Range
       Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    End With
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...