Find last row, Offset

[YellowLabPro]

This is a two part question.
Depending on how the first part is answered is if I need to ask the second part.

I have a sheet with 133 rows, rows 5 to 15 is header information and 16 to 133 is the range that can hold data input.
The majority of the time I do not use the entire range, so I hide the unused rows in the range.
I do this w/ separate procedure- HideRowsOrderInfo.

I want to wrap an If Condition around the called procedure, that if it has already been run, then skip it, if it has not been run, then run it.
The way I will determine if the procedure needs to be run is if there are any blank rows between the lastrow and A16.

This returns an error expecting an array. I posted to show my approach.
There may be other issues to resolve, but I am taking them one at a time.

Row 133 has static data in it to provide a stop for the .end.


lrow = Cells.SpecialCells(xlCellTypeVisible(Rows.Count, 1)).End(xlUp).Row

If Range("A" & lrow - 1) = "" Then
Call HideRowsOrderInfo
End If

[Norie]

Doug

I'm not 100% sure what you actually want to do with that code but when you hide a row it still has values in it.

[YellowLabPro]

If I forget to run the hide procedure, when I run the next procedure if there are blank rows between 16 and 133 then run the procedure.

[lucas]

So you basically want to check to see if there are already any hidden rows?

[YellowLabPro]

Hi Steve,
Yes. That is correct. That is where I was thinking the SpecialCells would step in.

[Bob Phillips]

How about something like



lRow = Cells(Rows.Count, "A").End(xlUp).Row
If lRow > 15 Then
If Range("A16").Resize(lRow - 15).SpecialCells(xlCellTypeVisible).Rows.Count >= lRow - 15 Then
Call HideRowsOrderInfo
End If
End If

[YellowLabPro]

Bob,
Right now the rows are hidden. The last row count is 133. So the code is executing the IF condition.
I might be totally off point here, but what I was thinking was if it possible to count only the visible cells.
If so then if it finds a blank cell between the last used cell and B15 then execute the IF condition.
But I am struggling w/ this a bit today.


lrow = Cells.SpecialCells(xlCellTypeVisible(Rows.Count, 1)).End(xlUp).Row

[YellowLabPro]

Here are a couple of screen shots which might help my challenged writing skills today.

http://img503.imageshack.us/img503/78/unhiddentz7.th.jpg (http://img503.imageshack.us/my.php?image=unhiddentz7.jpg)
http://img503.imageshack.us/img503/1149/hiddensq1.th.jpg (http://img503.imageshack.us/my.php?image=hiddensq1.jpg)

[Bob Phillips]

That is what I tried to do, count the visible cells, and if was greater than the number of cell in column A of that range, there are no non-visible cells.

Blanks don't seem to figure as I understand it.

[YellowLabPro]

Thanks Bob.
I don't know if this factors in to the equation/your solution. There are values in column A, row numbers all the way down.
I will keep playing around w/ this.

Thanks for looking into this.