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
.
.
31

now it seems simple but i want to return the specified day of month row that has 1 in operation column.

so for example

2,3,4 and 7,8

ive been picking my brain all day and cant come up with a conclusion.

basically there are typical groups e.g 2,3,4 and 7,8

the final thing i want is to a put the results in a cell and "possiblity" use trim

because the end result i want is a range for the days that were worked so far

so

2,3,4 = 2-4 and 7,8 = 7-8

problem is it always needs to be in the format containing 2 digits

2-4 and 7-8

maybe a mod function is in order?

i have the following functions so far

code:

Option Explicit

Sub FindOpDays()
Dim LastRow As Long, r As Long, c As Integer

LastRow = Range("A65536").End(xlUp).Row
c = 6
For r = 2 To LastRow
If Cells(r, 2) = 1 Then
Cells(2, c) = Cells(r, 1)
c = c + 1
End If
Next r
End Sub



any help would be great

Hello Admanirv,

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

Please Login or Register  to view this content.

Sincerely,
Leith Ross

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

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

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

Excellent thanks that worked!

Thanks for the feedback.

Mangesh