Thread: Named Range Change Based On AutoFilter

I'm creating an excel sheet and I need to use the autofilter on my list based on another cell.

For example, if A1 is L, then the AutoFilter filters the results of the first column for L types.

Then I need that selection of L values from the AutoFilter to be a named range.

Thanks.

assume the upper left corner of your data is in B9, then:

Sub ABC()
Dim rng As Range, rng1 As Range
Range("B9").CurrentRegion.AutoFilter _
Field:=1, Criteria1:=Range("A1").Value
Set rng = ActiveSheet.AutoFilter.Range
If rng.Columns(1).SpecialCells(xlVisible).Count > 1 Then
With rng.Columns(1)
Set rng1 = .Offset(1, 0).Resize( _
.Rows.Count - 1, 1).SpecialCells(xlVisible)
rng1.Name = Range("A1").Value
End With
End If


End Sub

--
Regards,
Tom Ogilvy


"SanctifiedRock" wrote:

>
> I'm creating an excel sheet and I need to use the autofilter on my list
> based on another cell.
>
> For example, if A1 is L, then the AutoFilter filters the results of the
> first column for L types.
>
> Then I need that selection of L values from the AutoFilter to be a
> named range.
>
> Thanks.
>
>
> --
> SanctifiedRock
> ------------------------------------------------------------------------
> SanctifiedRock's Profile: http://www.excelforum.com/member.php...o&userid=34485
> View this thread: http://www.excelforum.com/showthread...hreadid=542474
>
>

Thanks, but the line in red seems to be causing an error.

Sub ABC()
Dim rng As Range, rng1 As Range
Range("A12").CurrentRegion.AutoFilter _
Field:=1, Criteria1:=Range("A11").Value
Set rng = ActiveSheet.AutoFilter.Range
If rng.Columns(1).SpecialCells(xlVisible).Count > 1 Then
With rng.Columns(1)
Set rng1 = .Offset(1, 0).Resize( _
.Rows.Count - 1, 1).SpecialCells(xlVisible)
rng1.Name = Range("A11").Value
End With
End If


End Sub

I can't see any line in red, but your filter area should be separated on all
sides by a blank row/column. so having a value in A11 and trying to start
your filter in A12 will cause a problem with using CurrentRegion. either
Isolate your data or specify exactly what range you want to apply the filter
to. with isolated data the code ran fine for me.

--
Regards,
Tom Ogilvy


"SanctifiedRock" wrote:

>
> Thanks, but the line in red seems to be causing an error.
>
> Sub ABC()
> Dim rng As Range, rng1 As Range
> Range("A12").CurrentRegion.AutoFilter _
> Field:=1, Criteria1:=Range("A11").Value
> Set rng = ActiveSheet.AutoFilter.Range
> If rng.Columns(1).SpecialCells(xlVisible).Count > 1 Then
> With rng.Columns(1)
> Set rng1 = .Offset(1, 0).Resize( _
> .Rows.Count - 1, 1).SpecialCells(xlVisible)
> rng1.Name = Range("A11").Value
> End With
> End If
>
>
> End Sub
>
>
> --
> SanctifiedRock
> ------------------------------------------------------------------------
> SanctifiedRock's Profile: http://www.excelforum.com/member.php...o&userid=34485
> View this thread: http://www.excelforum.com/showthread...hreadid=542474
>
>