Range using variables

[timpie_s]

I am trying to set a range for autoselect sorting keeping in mind that someone (worksheet is used
by several people) is inserting colums and or rows (which would break the code and has happened
a few times):

Therefor I've created a function Kolom that retrieves the column name.
ColumnHeaders is defined as Traject!$10:$10 (10th row containing Column titles)

I've succesfully coded the firstcel as follows: (it returns $R$10) but to determine the range
using .end(xldown) until last used row in column doesn't work for me. What am i doing wrong?
Keep getting "Runtime error 1004" Method Range of Object '_Global' failed

Dim FirstCell, FilterRange As Range

FirstCell = Range(Kolom("Ploeg") & Range("ColumnHeaders").Row).Address
Msgbox FirstCell

FilterRange = Range(Range(FirstCell), Range(FirstCell).End(xlDown))

 

[Norie]

Why aren't you using Set for the FilterRange range object?

 

[timpie_s]

I did try this ...; same result .. how would you code it?

Why aren't you using Set for the FilterRange range object?

 

[Norie]

Assuming 'Ploeg' is the header of the column you want to find perhaps something like this.

Dim Res As Variant
  
    Res = Application.Match("Ploeg", Rows(10), 0)


    If Not IsError(Res) Then
        Set FilterRange = Range(Cells(10, Res), Cells(Rows.Count, Res).End(xlUp))
    End If

PS I'd also include worksheet references for Range/Cells to make sure the code was referring to the correct sheet.

 

[timpie_s]

Thankx!

Assuming 'Ploeg' is the header of the column you want to find perhaps something like this.

Dim Res As Variant
  
    Res = Application.Match("Ploeg", Rows(10), 0)


    If Not IsError(Res) Then
        Set FilterRange = Range(Cells(10, Res), Cells(Rows.Count, Res).End(xlUp))
    End If

PS I'd also include worksheet references for Range/Cells to make sure the code was referring to the correct sheet.