VBA_Offset for Column with Header

Question

I've a data where I applied filter to the column based on its header. Now I want to make some changes to filtered data i.e. visible cells in the same column. I've below mentioned code where filter has been applied to column with header "Query Type" & it's column letter is "E". Is it possible to put offset based on the column header instead of column letter? Because column gets changing everytime. In below example, how E2 or E can be replaced dynamically to accommodate column with header? I tried replacing "E" with FiltCol; however it is not working.

Sub Filter()

Dim FiltCol As Variant

FiltCol = Rows("1:1").Find(What:="Query Type", LookAt:=xlWhole).Column

ActiveSheet.UsedRange.AutoFilter Field:=FiltCol, Criteria1:="Rejected"

ActiveSheet.Range("E2", Range("E" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "Accepted"

End Sub

FunThomas · Accepted Answer · 2021-04-01 09:00:56Z

When you want to deal with column numbers, you can use the .Cells-property of the worksheet. Cells expects 2 parameters, row and column. The row is always a (long) number, the column can be specified as number or with the column character(s)

The following terms are all the same:

ActiveSheet.Range("D3")
ActiveSheet.Cells(3, 4)       
ActiveSheet.Cells(3, "D")

Your code could look like

Sub Filter()
    Dim FiltCol As Variant

    With ActiveSheet
        FiltCol = .Rows("1:1").Find(What:="Query Type", LookAt:=xlWhole).Column
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, FiltCol).End(xlUp).row
        .UsedRange.AutoFilter Field:=FiltCol, Criteria1:="Rejected"

        Dim visibleCells As Range
        On Error Resume Next    ' Avoid runtime error if nothing is found
        Set visibleCells = .Range(.Cells(2, FiltCol), .Cells(lastRow, FiltCol)).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        If Not visibleCells Is Nothing Then
            visibleCells.Value2 = "Accepted"
        End If
    End With
End Sub

Amiga500 · Accepted Answer · 2021-04-01 09:08:45Z

0

cells(1,1).offset(2,3)

will get you from A1 to D3

lastRow = Cells(105000, FiltCol).End(xlUp).Row    <<< This is poor, see edit below

ActiveSheet.Range(Cells(2, FiltCol).Offset(0, 1), Cells(lastRow, FiltCol).Offset(0, 1)).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "Accepted"

edit:

Better to dynamically describe the last row

lastRow = Cells(Cells.Rows.Count, FiltCol).End(xlUp).Row

edited Apr 1, 2021 at 9:08

answered Apr 1, 2021 at 8:47

Amiga500

1,2851 gold badge7 silver badges12 bronze badges

4 Comments

Amiga500 Over a year ago

I just grabbed a big number - it should really dynamically be bottom of the sheet. Edited in now.

FunThomas Over a year ago

While you are editing: You should never work with unqualified Ranges and Cells.

Amiga500 Over a year ago

Typically, I'd use Workbooks().Worksheets().cells() - otherwise its just asking for trouble - but I try to avoid chucking too much stuff up at once on here in the interests of conciseness.

FunThomas Over a year ago

A simple With-statement eases the pain. But ActiveSheet.Range(Cells... is dangerous because you specify the sheet for the Range but not for Cells. If you use such a statement and decide later to work with real worksheet variables, the line will fail and it's not easy to see why. SO is full of questions about that...

Collectives™ on Stack Overflow

VBA_Offset for Column with Header

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related