How do I access a cell's content that is outside of a filtered range in Excel?

This code will go through a filtered range and insert only the visible cells into an array (pretend column A was filtered according to my criteria). BUT, what I really want to do is shift one column over and insert the contents of "B3" into my array instead of "A3". How do I modify my code to this?

For Each cell In Range.SpecialCells(xlCellTypeVisible)
    Array1(i) = cell.Value
    i = i + 1
Next c

I was thinking something like Array1(i) = Cells(cell.Row, cell.Column + 1).Value

Answers


For Each cell In Range.SpecialCells(xlCellTypeVisible)
    Array1(i) = cell.Offset(ColumnOffset:=1).Value
    i = i + 1
Next c

For Each cell In Range.SpecialCells(xlCellTypeVisible)
   Array1(i) = cell.Offset(0, 1).Value
   i = i + 1

Next c

Since column B is going to be subject to the same filter as Column A, here's a flexible solution that allows you to specify which column you want to use:

Sub FilterColumn(ColumnNumber As Long)
Dim LastRow As Long
Dim rng As Range
Dim rngVisible As Range
Dim cell As Range
Dim Array1() As Variant
Dim i As Long

With ActiveSheet
    Set rng = .Columns(ColumnNumber)
    LastRow = .Cells(.Rows.Count, ColumnNumber).End(xlUp).Row
    On Error Resume Next
    Set rngVisible = .Range(.Cells(2, ColumnNumber), .Cells(LastRow, ColumnNumber)).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not rngVisible Is Nothing Then
        ReDim Preserve Array1(1 To rngVisible.Cells.Count)
        i = 1
        For Each cell In rngVisible
            Array1(i) = cell.Value
            i = i + 1
        Next cell
    End If
End With
End Sub

Call it like this for column B:

FilterColumn 2

As a side note, I'd suggest you don't use Excel reserved words for variable names. Range is a reserved word.


Need Your Help

Using .NET CodeDOM to declare and initialize a field in one statement

.net codedom

I want to use CodeDOM to both declare and initialize my static field in one statement. How can I do this?

send smtp email through godaddy

email smtp godaddy confirmation

So, I'm trying to send an smtp email from my website as a confirmation that their order has been place. The site is hosted on godaddy and I have no idea what's going on. I'm getting all kinds of er...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.