Facing issue while setting range

[Visor]

Dear friends of the forum, some time ago, I received support with a macro vba excel theme and it worked fine but now it stopped working.
Can you give me any idea of what might have happened?

This is de code

Sub FilterList(xLet As String)
Dim rngFilter As Range
'Application.ScreenUpdating = False
'What range are we going to filter?
Set rngFilter = Range("B8").CurrentRegion

'Resize to give a header row
Set rngFilter = rngFilter.Offset(-1).Resize(rngFilter.Rows.Count + 1)

'Apply Filter
ActiveSheet.AutoFilter.ShowAllData
rngFilter.AutoFilter field:=1, Criteria1:="=" & xLet & "*"
'Application.ScreenUpdating = True
End Sub

The code gives me error in:

Set rngFilter = rngFilter.Offset(-1).Resize(rngFilter.Rows.Count + 1)

The link where I received support was this:

http://forum.chandoo.org/threads/sh...letter-of-the-commanbutton.29517/#post-176430

Until a certain time it worked well in the work file, now it does not ... I am very thankful for your comments.

 

[NARAYANK991]

Hi ,

The problem is the use of the keyword CurrentRegion ; this is generally used when programmers are not too keen on defining the exact range which needs to be operated on.

What the keyword CurrentRegion does is to identify your data range by the presence of blank columns and rows. This may not always be what you want.

Suppose the range that Excel has identified is A1:H43 ; now , in this statement , there is an Offset(-1) used ; since we are already using row 1 as a part of the data range , offsetting 1 row negatively will take us beyond the boundaries of what is valid , and hence an error will be generated.

To identify this , step through the code , and before you execute the statement which is generating the error , in the Immediate window , type in :

?rngFilter.Address

and see what is displayed. If row 1 is included in the address , then the error is because of this ; to resolve this problem , remove the Offset(-1) from the offending statement.

Narayan

 

[Visor]

Thanks, actually deleting Offset (-1) solved the problem, now it works correctly.
I'm going to check it out to understand everything you explain to me in a practical way.
I am very grateful for the support. If in the later it presents / displays some problem raise again the subject, I say it because the previous code I worked practically 8 months and soon it failed.

 

[ThrottleWorks]

Hi @Visor , please note changes title of your request. Good night.