CODEWITHSUNDEEP
excelvbaoffset
Satish
Satish

Reputation: 41

VBA_Offset for Column with Header

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

Upvotes: 0

Views: 133

Answers (2)

Amiga500
Amiga500

Reputation: 1275

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

Upvotes: 0

FunThomas
FunThomas

Reputation: 29466

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

Upvotes: 1

Related Questions