Changing ActiveCell in an autofilter VBA

[couch1971]

I am writing a userform that makes a listbox of unique values, which the user can choose, then hit another button and it will autofilter on the chosen value.

The next set of tasks involve adding some formulas into some of the cells in the filtered data. I know how to add formulas and such with VBA. My only problem is that I am unable to figure out how to move the active cell into the filtered range....

The filtered column is I. In my code to create the unique list, I move the activecell to "I1" and create the list from there. The active cell stays at I1 through the process until the listbox pops up. I choose a value, hit OK, and it filters just fine......

How do I move the active cell down to the top cell of column J of the filtered list?

[Jacob Hilderbrand]

You can use Offset to move the active cell. The syntax is Offset(#_Of_Rows, #_Of_Columns). Positive values change the range down and to the right, negative values change it up and to the left.

So to move to column J you can use this:

ActiveCell.OffSet(0,1).Select

Note that you should avoid selecting cells if you don't have to. If you want to put a formula in J2 for example you can use this:

Range("J2").Value = MyFormula

This will allow you to put the value or formula into the cell without changing the active cell.
You could also use OffSet to put a formula two rows down and one column across like this:

ActiveCell.OffSet(2,1).Value = MyFormula

[couch1971]

You can use Offset to move the active cell. The syntax is Offset(#_Of_Rows, #_Of_Columns). Positive values change the range down and to the right, negative values change it up and to the left.

So to move to column J you can use this:

ActiveCell.OffSet(0,1).Select

Note that you should avoid selecting cells if you don't have to. If you want to put a formula in J2 for example you can use this:

Range("J2").Value = MyFormula

This will allow you to put the value or formula into the cell without changing the active cell.
You could also use OffSet to put a formula two rows down and one column across like this:

ActiveCell.OffSet(2,1).Value = MyFormula

Jacob,

I have tried the offset, and I know the movement, but it does not work, when I have filtered the column to a value, and the visible rows now start at ie: 364. So i have rows 1 and 364:370 now visible from the filter. How do I get the activecell to J364......under the assumption that I never know what rows will be visible after each filter?

[Jacob Hilderbrand]

You can create a range of just the visible cells and loop through that range.

Dim FilteredRange   As Range
 Dim Cel             As Range
Set FilteredRange = Range("A:A").SpecialCells(xlCellTypeVisible)
     For Each Cel In FilteredRange
         'Do Something
     Next

[Daniel Klann]

Hi,

This code will select the first visible cell (excluding row 1) in column J. Let me know if it works for you.

Worksheets("Your Sheet").Range("J2:J65536").SpecialCells(xlCellTypeVisible).Cells(1, 1).Select

Dan