Microsoft Excel - Make a Range & Pass the Range to a Function

Dear Friends,

 I have the below data with me like below

0811	0911	1011	1111	1211	0112	0212	0312	0412	0512	0612	0712	0812	0912	1012	1112	1212	0113	0213	0313	0413	0513	0613
60	60	60	60	60		60	60	60	60	60	60	60	60	60	60	60
60	60	60		60	60	60	60	60	60	60			60	60	60	60	60	60	60

Headers are the months & year, and the rest of two rows are the payments of that particular month.

If u see in first row there are paymentsfrom 0811 to 1211 and 0212 to 1212, I means these are the two sets.
in second row its different.

Now, I would want to find the Header of the start of the payent and end of the payment, and I will pas them to a function.

according to the first row, I am looking for output 0811 to 1211. First set, Now I will pass these range to one function to check that really payments are existed or not, once I confirm that payments are there, then next from the first I can see that from 0212 to 1212 one more set is there, Now I will pass this set the same function to check the payments are there or not.

could you please help me out how can I build a logic to get the set of the months based on the payments that are paid.

thanks

Regards,
Prasad        

Hello prasad,
The following piece of code might get you going.
Option Explicit

Sub rangefind()

Dim rngRangeFound, rngRangeDate, rngCell As Range
Dim intCellFirst, intCellLast As Integer

Set rngRangeDate = Range(Range("A1"), Range("A1").End(xlToRight))
intCellFirst = rngRangeDate.Cells(1, 1).Column

For Each rngCell In rngRangeDate
If Not (IsEmpty(rngCell.Offset(1, 0))) Then GoTo 100
intCellLast = rngCell.Column - 1
If intCellFirst - intCellLast = 1 Then GoTo 99
Set rngRangeFound = Range(rngRangeDate.Cells(1, intCellFirst).Offset(1, 0), _
    rngRangeDate.Cells(1, intCellLast).Offset(1, 0))
MsgBox (rngRangeFound.Address & " is the current range found")
99:
intCellFirst = rngCell.Column + 1
100:
Next
End Sub

Regards
Harry

wow..that looks great, I want the headers of the same range..
thanks a ton

Regards,
Rajender

Hello Rajender,
Just remove the offset parts in the Set rngRangeFound statement.
Regards
Harry