finding range through empty rows

[yummy]

I have some tables on a sheet (rows and columns).

The tables are separated by an empty row each.

I want to specify the range as follows.
Calculate the number of empty rows in column A and as soon as i soon as I found the third empty row. I know that table 3 is below.

Then I want to specify the range on column J starting from below the row having third space (range start) and go down until again I found next space.

I want to call the range start as "iStart" and range End as "iEnd."

Can anybody tell me how to do it VBA?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG21Feb57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
 [COLOR="Navy"]If[/COLOR] Application.CountA(rows(Dn.row)) = 0 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        [COLOR="Navy"]If[/COLOR] c = 3 [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(1, 9) = "iStart"
        [COLOR="Navy"]ElseIf[/COLOR] c = 4 [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 9) = "iEnd"
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[yummy]

It is giving me a problem

under the code you provided, I am trying to do this


For i = iStart To iEnd
Select Case Range("D" & i).Value
Case "Urgent"

Urgent = Urgent + 1

Case "High"

High = High + 1

Case "Normal"

Normal = Normal + 1

Case "Low"

Low = Low + 1

Case Else

End Select

Next i

an error occurs that "range failed"
Is there any way I can solve this problem ?

 

[MickG]

Perhaps this sort of thing ???

[COLOR=navy]Sub[/COLOR] MG21Feb12
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] iStart [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] iEnd [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
 [COLOR=navy]If[/COLOR] Application.CountA(rows(Dn.row)) = 0 [COLOR=navy]Then[/COLOR]
        c = c + 1
        [COLOR=navy]If[/COLOR] c = 3 [COLOR=navy]Then[/COLOR]
           iStart = Dn.row
        [COLOR=navy]ElseIf[/COLOR] c = 4 [COLOR=navy]Then[/COLOR]
           iEnd = Dn.row
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy][/COLOR] 
[COLOR=navy]Dim[/COLOR] Urgent [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] High [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Normal [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Low [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy][/COLOR] 
[COLOR=navy]For[/COLOR] i = iStart To iEnd
[COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] Trim(Range("D" & i).value)
[COLOR=navy]Case[/COLOR] "Urgent"
Urgent = Urgent + 1
[COLOR=navy]Case[/COLOR] "High"
High = High + 1
[COLOR=navy]Case[/COLOR] "Normal"
Normal = Normal + 1
[COLOR=navy]Case[/COLOR] "Low"
Low = Low + 1
[COLOR=navy]Case[/COLOR] [COLOR=navy]Else[/COLOR]
[COLOR=navy]End[/COLOR] Select
[COLOR=navy]Next[/COLOR] i
MsgBox "Urgent = " & Urgent & vbLf _
    & "High = " & High & vbLf _
    & "Normal = " & Normal & vbLf _
    & "Low = " & Low
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[yummy]

Thanks

It worked

Thanks a lot !