Complicated Find Question!

[Karoo News]

I have a sheet that is part of a staffing rota. The hours people work are
filled out by clicking the mouse and dragging it along the row which places
a " " (space) in each cell highlighted and changes the colour to Grey using
VBA. I currently use lookup formulas to find the first space (start time)
and the last space (end time) the problem is that to look for the last space
I have to have a mirror image of the selection area as the lookup does not
work in reverse so in theory the end space in the reversed area is actually
the first not the last space. (I used spaces to eliminate the need for
conditional formatting)

Is there some VBA code or a Formula that would find the last space within
the row bearing in mind there is blanks in the middle for lunch hour! I need
this to reduce the file size as there is 57 sheets in the workbook of which
35 are used for each day of the month although only one week shows at a
time, and quite a lot of code is in the workbook as each month has different
start days of the week so when the month changes the sheets are changed and
some hidden depending on the how many days are in the month which is why I
need help on this problem.....

EG i'm using x's here to show where spaces would be.


7.30 8.00 8.30 9.00 9.30 10.00 10.30 11.00 11.30 12.00 12.30 1.00
1.30 2.00 2.30 3.00 3.30 4.00 etc
Name1
Name2
Name3 x x x x x x x x
x x x x x x
Name4
Name5
Name6
Name7
etc

The above would give start time as 8.00 and end time as 4.00 (last x in
3.30)

A formula would probably be better at the end of each row due to the fact
that if 5 people are starting at the same time the mouse can highlight and
fill out 5 rows at once!

Hope this makes sense!!!!

Regards
Neil

 

[Tom Ogilvy]

VBA could be something like this:

Dim rng as Range, rng1 as Range
Dim i as Long Dim lastcell as Range
for i = 2 to 4
set rng = Cells(2,i).Resize(1,30)
On Error Resume Next
set rng1 = rng.specialcells(xlConstants,xlTextValues)
On Error goto 0
if not rng1 is nothing then
set lastcell = rng.Areas(rng.Areas.count)
set lastcell = lastcell(lastcell.count)
msgbox "Row " & i ": " & lastcell.Address
end if
next

A formula could be:

=MATCH(REPT("z",255),4:4)

for row 4.

 

[Karoo News]

That that formula is fantastic many thanks again does the job perfect