Find ranges of rows that contain 1
 

Hi basically i have a simple structure like this in excel


day of month operation
1
2 1
3 1
4 1
5
6
7 1
8 1
9

Find ranges of rows that contain 1
 

Hello Admanirv,

This code does what you want. It starts at "B2" looking for a 1 in tha
column. If a I is present it will build the days worked string a
outlined. The string will then be stored starting at "F2", then "G2"
etc.


Code
-------------------
Public Sub FindOpDays()

Dim N, D
Dim I As Long
Dim LastRow As Long
Dim FirstDay As Integer

LastRow = Range(\"A65536\").End(xlUp).Row

For I = 2 To LastRow
N = Cells(I, \"B\").Value
D = Cells(I, \"A\").Value
If N = 1 Then
If FirstDay = 0 Then FirstDay = D
Else
If FirstDay < 0 Then
Cells(2, \"D\").Offset(0, I).Value = Trim(Str(FirstDay)) &\" - \" & Trim(Str(D))
FirstDay = 0
End If
End If
Next I

End Su
-------------------


Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=37411

Find ranges of rows that contain 1
 

thanks for that allthough it just displays one date in k2, ie 6mar.

thats doesnt have a 1 in it.

I suppose an outlined function would be

checked through column b, starting from b2 check for existences of 1
and find the start of 1 and end of 1 for cells lentgh greater than 1.
an return the rows in a that have existence of 1 in. so....


2 columns below d.of month and op

for example

d.of month | op

1
2 1
3 1
4 1
5
6
7 1
8 1
9 1
10
11

that would equal two results

2-4 and 7-9.

thanks for you reply so far


--
admanirv
------------------------------------------------------------------------
admanirv's Profile: http://www.excelforum.com/member.php...o&userid=23746
View this thread: http://www.excelforum.com/showthread...hreadid=374117

Find ranges of rows that contain 1
 

Hi Leith,

a minor modification in your code:


Public Sub FindOpDays()

Dim N, D
Dim I As Long
Dim LastRow As Long
Dim FirstDay As Integer

LastRow = Range("A65536").End(xlUp).Row
j = 0

For I = 1 To LastRow + 1
N = Cells(I, "B").Value
D = Cells(I, "A").Value
If N = 1 Then
If FirstDay = 0 Then FirstDay = D
Else
If FirstDay < 0 Then
Cells(2, "D").Offset(0, j).Value = Trim(Str(FirstDay)) & " to " &
Trim(Str(Cells(I - 1, "A").Value))
FirstDay = 0
j = j + 1
End If
End If
Next I

End Sub


This was necessary as your code was returning 2-5 and 7-9. Also it
failed when there was a "1" for the last row.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374117

Find ranges of rows that contain 1
 

Hi admanirv

thanks for that allthough it just displays one date in k2, ie 6mar.

You get the date, as the code given by Leith returns a 2-5 which excel
converts to date. I have modifed that code to return 2 to 4 instead of
2 - 4. Another method would be to return '2-4 telling excel explicitly
that this is a text and not date

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374117

Find ranges of rows that contain 1
 

Excellent thanks that worked!


--
admanirv
------------------------------------------------------------------------
admanirv's Profile: http://www.excelforum.com/member.php...o&userid=23746
View this thread: http://www.excelforum.com/showthread...hreadid=374117

Find ranges of rows that contain 1
 

Thanks for the feedback.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374117