PDA

View Full Version : ActiveCell.Offset in a Data filtered sheet



huntson
06-09-2012, 11:56 AM
I have a data filter setup in excel so this means that sometimes the cells shown are not continuous - it might go from cell 5 to 7 to 100. I want to insert spaces in-between these cells. When I use

ActiveCell.Offset(-1, 0).EntireRow.Select

It obviously goes in between 5 and 7 and inserts a total of two spaces because it wants to touch 6 as well. Is there a way to have it just jump to what the data filter presents as the next row and not what really should be the next row?


I am new to this whole thing so I hopefully explained it well. Let me know if you have any questions.

huntson
06-09-2012, 12:18 PM
It appears something like this:

do
activecell.offset(1,0).select
if activecell.entirerow.hidden = false then
exit do 'found a visible cell
end if
loop

was necessary

Benzadeus
06-09-2012, 02:18 PM
Sub Example()
Dim l As Long
Dim lLast As Long

lLast = Cells(Rows.Count, "A").End(xlUp).Row
'Considering your data begins at row 2
For l = lLast To 2 Step -1
If Rows(l).Hidden = False Then
'Code here to act on visible rows, e.g.:
Rows(l - 1).Insert
Else
'Code here to act on invisible rows
End If
Next l
End Sub

huntson
06-09-2012, 02:22 PM
Again - I am very new to this stuff. Can you explain your code please. WHat is wrong with the solution I found?

Benzadeus
06-10-2012, 07:27 AM
In the code I wrote, see there is a conditional test line, using the If clause.
The condicional test verifies if the worksheet row l is hidden or not. The row l varies from the last line of column A to the second line, because I am considering one row of header. In other words, the code between For l = ... until Next l runs several times (or, to be more specific, runs the number of rows column A has).
Backing to the conditional test, it will do an action if the row is hidden, else, it will do another action.