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.


--
SanctifiedRock
------------------------------------------------------------------------
SanctifiedRock's Profile: http://www.excelforum.com/member.php...o&userid=34485
View this thread: http://www.excelforum.com/showthread...hreadid=542474

Named Range Change Based On AutoFilter
 

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

Named Range Change Based On AutoFilter
 

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

Named Range Change Based On AutoFilter
 

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