CODEWITHSUNDEEP
vbaexcelrange
Xepos
Xepos

Reputation: 167

How to get the value of a range within a range

So I need to extract information from a sheet with only certain values. From about 550 rows down to 50 which are spread across the entire sheet.

So I used autofilter for that. Now I only see the rows which match to my criteria but how can I get the values of a specific range from?

This far I came:

I know that I have to use 

RangeINamed.SpecialCells(xlCellTypeVisible)

to work with only the visible information. It worked for getting the starting and last row 

startRow = bulkbatchRange.SpecialCells(xlCellTypeVisible).row
endRow = startRow + bulkbatchRange.SpecialCells(xlCellTypeVisible).rows.Count

But now I need to get the value of a specific column, I want to use a For loop so I can loop through all visible rows.

So I tried to do 

RangeINamed.SpecialCells(xlCellTypeVisible).range("U" & rowNumber).value

That didn't work it gave me nothing. Now I'm rather clueless so does someone maybe know how I get the value of that row in column U in RangeINamed?

Thank you

Upvotes: 3

Views: 1729

Answers (2)

Makah
Makah

Reputation: 4523

I think you need to choose if you want to get a specific cell like:

Range("U10").Value

Or a relative cell using something like

RangeINamed.SpecialCells(xlCellTypeVisible)(2,3).Value

Or

RangeINamed.SpecialCells(xlCellTypeVisible)(2,3).Address 'To see if you are getting it right

EDIT:

A complete code to Filter and Iterate.

Sub Filter()
Dim tableRange As Range, var, actualRow As Integer, lastRow As Integer

Set tableRange = Range("PUT_THE_TABLE_RANGE_HERE")

' Filter 
With tableRange
    Call .AutoFilter(5, "SPECIFIC_FILTER")
End With

Set f = tableRange.SpecialCells(xlCellTypeVisible)

With tableRange
    Call .AutoFilter(5)
End With

For Each var In f.Cells.Rows
    actualRow = var.Row
    If actualRow <> 1 Then
        ' Do something
    End If
Next
End Sub

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

You can always retrieve the value in a specific cell like U10 with:

Range("U10").Value

whether the row is hidden or not.

EDIT#1:

Here is a little example that loops down thru column A of an AutoFiltered table. It looks for the third visible row (not including the header row):

Sub GoDownFilter()
    Dim rLook As Range, r As Range
    Set rLook = Intersect(ActiveSheet.UsedRange, Range("A:A").Cells.SpecialCells(xlCellTypeVisible))
    rLook.Select
    K = 0
    For Each r In rLook
        If K = 3 Then
            r.Select
            MsgBox "The third visible row has been selected"
            Exit Sub
        End If
        K = K + 1
    Next r
End Sub

Upvotes: 2

Related Questions