PDA

View Full Version : [SOLVED] Changing ActiveCell in an autofilter VBA



couch1971
01-18-2005, 07:22 AM
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
01-18-2005, 07:44 AM
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
01-18-2005, 10:01 AM
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
01-18-2005, 10:42 AM
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
01-18-2005, 07:33 PM
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