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
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:
As a side note, I'd suggest you don't use Excel reserved words for variable names. Range is a reserved word.