Excel VBA - Issue in Naming Filtered Range on a Filtered List.

With one cell filtered-out you got problems here:

        For Each r In rG
        
            r.Select
            'ActiveCell.Offset(1, 0).Select
            grade = ActiveCell.Value
            
            ActiveCell.Offset(0, 1).Select
            'Range(ActiveCell, ActiveCell.End(xlDown)).Select
            Range(ActiveCell, ActiveCell.End(xlDown)).Select

because your Activecell had nothing visible in next rows

Range(ActiveCell, ActiveCell.End(xlDown)).Select

selected everything from the current cell down to the last row.
could be that oposite direction - the bottom-up could work right

Range(ActiveCell, cell(rows.count,ActiveCell.column).End(xlup)).Select

but I'd rather go for not selecting, but using already found range:

Sub GetUniqueGrades_and_Looping()

    Dim unikaty(), dane()
    Dim ile&, i&, j&, x&
    Dim rGrades As Range, rG As Range
    Dim grade As String
    
    ThisWorkbook.Activate
    Sheets("GradesNames").Activate
    ile = Cells(Rows.Count, 1).End(xlUp).Row
    dane = Application.Transpose(Range("A2:A" & ile))

    For i = 1 To ile - 1
        For j = 1 To x
            If dane(i) = unikaty(j) Then Exit For
        Next j
        
        If j = x + 1 Then
            x = x + 1
            ReDim Preserve unikaty(1 To x)
            unikaty(x) = dane(i)
        End If
    Next i

    Set rGrades = Cells.Find(What:="Grades", After:=Range("A1"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    
    For i = 1 To x
        ActiveSheet.Range("$A$1:$B$1").AutoFilter Field:=rGrades.Column, Criteria1:=unikaty(i)
  
        Set rG = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        grade = rG(1).Value
        ActiveWorkbook.Names.Add Name:=grade, RefersTo:=rG.Offset(0, 1)
        Selection.AutoFilter
    Next i
    ActiveSheet.Range("$A$1:$B$1").AutoFilter
    ActiveWorkbook.Save

End Sub

Dear Kaper,

Once again, it's Kaper who has solved my problem!! Brilliant and Efficient

Now that you have helped me group the Names and name the range.

Need you help with Naming entire Grades List.

I can understand that unikaty(i) at each loop will give me each grade.

I presume dane() has the GradesList.

Is it possible to consider the array of values as a Range and Name them?

If so, please help with the code {Would love if it is a few line code}

Need to implement same logic at work on a huge set of data.

Hence the request for efficient-come-few lines code

Thanks,
Vinod Krishna

Hi,

The code works also for large data sets.
Unikaty holds as many (well there are limits of Visual Basic - but for sure not easy to reach with this class of problems) unique grades values as needed.
Try it!
If the data is organised the way it was in the sample file, code would work.

Press F3 and you can get a list of named ranges as they are defined.
Do with these named ranges just what you want (I hope you know what you want - otherwise we both just lost time).

Hi Kaper,

Thank you. I will try based on your suggestion

I think this would be a new requirement and I will raise a new post for the same.

Thanks,
Vinod Krishna