Excel vba: extract characters before delimiter in active cell

[2Took]

Hi,

Not looking for a Function.

1. Need VBA to extract left characters before delimiter "." in active cell and call it ExL
2. Need VBA to extract right characters before delimiter " - " in active cell and call it ExR

Got code below, but those are functions, don't need that:

If InStr(txt, char) > 0 Then Extract_LString = Left(txt, InStr(txt, char) - 2)

If InStr(txt, char) > 0 Then Extract_RString = Right(txt, InStr(txt, char) - 4)

 

[2Took]

What range are you trying to filter? H10:H20 for example

I think I follow you, I need something like below code snippet (completely unrelated) to tell XL what range to filter...
1. How do I edit above code to tell it to filter UsedRange, relative to the cell that code returns, H10 in above example? (If UsedRange is going to include the corresponding data table)
2. And, guessing field:=2 in below would refer to Column H in my example above? Is there easier way to refer to it by the Column letter, than by its corresponding number?

How would you edit the above code with these things in mind?

ActiveSheet.Range("B4:E15").AutoFilter field:=2

 

[2Took]

EDIT: "field:=" would need to be ExL

 

[johnnyL]

You basically need 3 things for Filter:
1) The full range of data that you want to filter ... in your example from your last post, the full range would be B4:E15
2) The column you want to set the filter for ... 2 from your example means the second column in the full range ... Column B is the first column so '2' would mean Column C
3) What you want to filter for ... this is the Criteria ... ExR from your previous code

I can't see your data so I can't guess what you are trying to filter.

 

[2Took]

You basically need 3 things for Filter:
1) The full range of data that you want to filter ... in your example from your last post, the full range would be B4:E15
2) The column you want to set the filter for ... 2 from your example means the second column in the full range ... Column B is the first column so '2' would mean Column C
3) What you want to filter for ... this is the Criteria ... ExR from your previous code

I can't see your data so I can't guess what you are trying to filter.

B4:E15 is unrelated, I was just using that code snippet to wrap my mind around the syntax. Sorry, confusion is all mine.
My data starts in A10, row 10 in where the table header is. Data goes down different number of rows - it's not consistent, and columns to the right may not be consistent in terms of how many.

What would be the code to be flexible enough to determine the range to filter automatically from, in a above example H10, something like UsedRange maybe?

re: "field:=" - Is there easier way to refer to it by the Column letter, than by its corresponding number?
"field:=" would need to be ExL, as it is variable

How would you edit the above code with these things in mind?

 

[johnnyL]

Is your data always going to start in Column A?

 

[2Took]

Is your data always going to start in Column A?

Yes, always in A

 

[johnnyL]

So something like the following:

    With ActiveSheet.UsedRange
        .AutoFilter Field:=Range(ExL & 1).Column, Criteria1:=ExR
    End With

This part is the part that converts the 'H' to the equivalent column number

Range(ExL & 1).Column

 

[2Took]

So something like the following:

    With ActiveSheet.UsedRange
        .AutoFilter Field:=Range(ExL & 1).Column, Criteria1:=ExR
    End With

This part is the part that converts the 'H' to the equivalent column number

Range(ExL & 1).Column

At the end it is so simple, so elegant.

 

[johnnyL]

Glad to help.

 

[2Took]

Glad to help.

don't kill me, but how do I also get the sheet so scroll to ExL Column?
Like scroll it into view