Row count, and cell addresses

E

e61

New Member
Joined
Jun 20, 2011
Messages
6
Consider table below:

<b>Excel 2007</b>
<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;;">1FA</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Club</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">3</td><td style="font-weight: bold;text-align: center;;">4</td><td style="font-weight: bold;text-align: center;;">5</td><td style="font-weight: bold;text-align: center;;">6</td><td style="font-weight: bold;text-align: center;;">7</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">9</td><td style="font-weight: bold;text-align: center;;">10</td><td style="font-weight: bold;text-align: center;;">iX</td><td style="font-weight: bold;text-align: center;;">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">1</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">2</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">3</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">4</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">5</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="font-weight: bold;;">1FB</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Club</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">3</td><td style="font-weight: bold;text-align: center;;">4</td><td style="font-weight: bold;text-align: center;;">5</td><td style="font-weight: bold;text-align: center;;">6</td><td style="font-weight: bold;text-align: center;;">7</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">9</td><td style="font-weight: bold;text-align: center;;">10</td><td style="font-weight: bold;text-align: center;;">iX</td><td style="font-weight: bold;text-align: center;;">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">1</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">2</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">3</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">4</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">5</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="font-weight: bold;;">1FC</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Club</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">3</td><td style="font-weight: bold;text-align: center;;">4</td><td style="font-weight: bold;text-align: center;;">5</td><td style="font-weight: bold;text-align: center;;">6</td><td style="font-weight: bold;text-align: center;;">7</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">9</td><td style="font-weight: bold;text-align: center;;">10</td><td style="font-weight: bold;text-align: center;;">iX</td><td style="font-weight: bold;text-align: center;;">Sum</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">1</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">2</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">3</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">4</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">5</td><td style=";">John Doe</td><td style=";">Tiger</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td></tr></tbody></table>

This is a part of a scoresheet.
The number of contestants can vary, in this example 5 in each competetion category named "1FA" - "1FC"
Categories can vary from: 1FA to 1FD + 1FU16

Problem:
I'm having a VBA routine to print out a html file of the scoresheet.
In some way, I need to find out the start/end address of each category,
IF they exists, and feed that back to the vba html routine.
The row count and start/end address for each existing competetion is what I need... :-)

Q's:
- How to find start/end addresses for each category, IF category exists?
- How to row count between start/end addresses for each category, IF category exists?

In this example table;
start/end of 1FA would be A14/A18, 5 rows
start/end of 1FB would be A20/A24, 5 rows
start/end of 1FC would be A26/A30, 5 rows
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
It would seem that you could check whether or not the value in column A is numerical with Excel's Type() function. If it's not, the start would be the cell directly below. The end position is then based on the number of numerical values until you either find a blank, or another non-numerical value.

Just make sure none of your numbers are stored as text.
 
Upvote 0
Try this:-
This will return a Msgbox with the Ranges and rows.
See code for Options.
Code: 
[COLOR=navy]Sub[/COLOR] MG22Jun50
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] oRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rw [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] fRw [COLOR=navy]As[/COLOR] Range
[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] Txt = "" And Left(Dn, 2) = "1F" [COLOR=navy]Then[/COLOR]
            Txt = Txt & Dn.Address & ":"
        [COLOR=navy]ElseIf[/COLOR] Left(Dn, 2) = "1F" And Not Dn.Address = Rng(Rng.Count).Address [COLOR=navy]Then[/COLOR]
            Txt = Txt & Dn.Offset(-1).Address & "," & Dn.Address & ":"
        [COLOR=navy]ElseIf[/COLOR] Dn.Address = Rng(Rng.Count).Address [COLOR=navy]Then[/COLOR]
            Txt = Txt & Dn.Address
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
'MsgBox Txt
 [COLOR=navy]Set[/COLOR] Rng = Range(Txt)
For Each oRng In Rng.Areas '[COLOR=green][B]Do something with each sub range[/B][/COLOR]
     rws = rws & oRng.Address & " Rows =  " & oRng.Count & chr(10)
 
       For Each fRw In oRng '[COLOR=green][B] do something with each Row in sub range[/B][/COLOR]
 
                '[COLOR=green][B]Do something here[/B][/COLOR]
 
        [COLOR=navy]Next[/COLOR] fRw
    [COLOR=navy]Next[/COLOR] oRng
MsgBox rws
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
@MickG:

This is close, thank you for your effort!

It does however, set start address one row to "low", if you get my grip:
it sets start 1FA as row A13, but it should be A14.
It does that for each category further down the sheet.
Any solution to correct that?

The end addresses for each category is correct.
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG22Jun42
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] fRw [COLOR="Navy"]As[/COLOR] Range
[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] Txt = "" And Left(Dn, 2) = "1F" [COLOR="Navy"]Then[/COLOR]
            Txt = Txt & Dn.Offset(1).Address & ":"
        [COLOR="Navy"]ElseIf[/COLOR] Left(Dn, 2) = "1F" And Not Dn.Address = Rng(Rng.Count).Address [COLOR="Navy"]Then[/COLOR]
            Txt = Txt & Dn.Offset(-1).Address & "," & Dn.Offset(1).Address & ":"
        [COLOR="Navy"]ElseIf[/COLOR] Dn.Address = Rng(Rng.Count).Address [COLOR="Navy"]Then[/COLOR]
        Txt = Txt & Dn.Address
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
'MsgBox Txt
 [COLOR="Navy"]Set[/COLOR] Rng = Range(Txt)
For Each oRng In Rng.Areas '[COLOR="Green"][B]Do something with each sub range[/B][/COLOR]
     rws = rws & oRng.Address & " Rows =  " & oRng.Count & chr(10)
       
       For Each fRw In oRng '[COLOR="Green"][B] do something with each Row in sub range[/B][/COLOR]
            
                '[COLOR="Green"][B]Do something here[/B][/COLOR]
            
        [COLOR="Navy"]Next[/COLOR] fRw
    [COLOR="Navy"]Next[/COLOR] oRng
MsgBox rws
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
@MickG

A follow up q, if you don't mind...

Your code find the rowcount for each category, as well as start/end address.
But, how do I know the names of wich categories found?

Let me try to explain:
Category names consist of 2 parts.
part1 is one of: 1F, 1G, 1M, 1R
part2 is one of: A, B, C, D, U16
So any combination of part1 and part2 is legal,
for instance 1FA, 1GB, 1FU16, 1RD, 1MC and so on..
There is no way to tell up front which of them exists in the final scoresheet.

So, there is also a need to figure out IF a category exist,
in addition to start/end address and how many rows there are for each existing category name.

I hope this explains what I'm aiming at... :-)
(Pardon my lousy english, not my native language)
 
Upvote 0
See Modified code:-
The code now returns the category Name, and Checks it against Those allowed.
If acceptable the code is continued.
Hopefully this is what you want.
Code: 
[COLOR=navy]Sub[/COLOR] MG08Jul12
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] oRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rw [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] fRw [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rws [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] nam [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] oLt
[COLOR=navy]Dim[/COLOR] oRt
[COLOR=navy]Dim[/COLOR] Fd [COLOR=navy]As[/COLOR] Boolean
[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] Txt = "" And Left(Dn, 2) = "1F" [COLOR=navy]Then[/COLOR]
            Txt = Txt & Dn.Offset(1).Address & ":"
        [COLOR=navy]ElseIf[/COLOR] Left(Dn, 2) = "1F" And Not Dn.Address = Rng(Rng.Count).Address [COLOR=navy]Then[/COLOR]
            Txt = Txt & Dn.Offset(-1).Address & "," & Dn.Offset(1).Address & ":"
        [COLOR=navy]ElseIf[/COLOR] Dn.Address = Rng(Rng.Count).Address [COLOR=navy]Then[/COLOR]
        Txt = Txt & Dn.Address
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
MsgBox Txt
 Fd = False
 [COLOR=navy]Set[/COLOR] Rng = Range(Txt)
For Each oRng In Rng.Areas '[COLOR=green][B]Do something with each sub range[/B][/COLOR]
'[COLOR=green][B]This returns column "A" Category Name[/B][/COLOR]
 nam = oRng(1).Offset(-1)
 MsgBox nam
'[COLOR=green][B]This code Creates all options Of Category Names , then[/B][/COLOR]
'[COLOR=green][B]test for "Nam", and proceeds if found.[/B][/COLOR]
ReDim Ray(1 To 20)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] oLt [COLOR=navy]In[/COLOR] Array("1F", "1G", "1M", "1R")
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] oRt [COLOR=navy]In[/COLOR] Array("A", "B", "C", "D", "U16")
        [COLOR=navy]If[/COLOR] nam = oLt & oRt [COLOR=navy]Then[/COLOR]
            Fd = True
            [COLOR=navy]Exit[/COLOR] For
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] oRt
[COLOR=navy]Next[/COLOR] oLt
    [COLOR=navy]If[/COLOR] Fd = True [COLOR=navy]Then[/COLOR]
 
     Rws = Rws & oRng.Address & " Rows =  " & oRng.Count & Chr(10)
 
       For Each fRw In oRng '[COLOR=green][B] do something with each Row in sub range[/B][/COLOR]
            MsgBox fRw.Address
                '[COLOR=green][B]Do something here[/B][/COLOR]
 
        [COLOR=navy]Next[/COLOR] fRw
    [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] oRng
MsgBox Rws
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
Mick, first of all, thanks for your effort in helping solving this, for me, an impossible task.

Please consider table below. It has all legal categories present, and for simplicity, only 2 rows for each category.

Excel 2007

<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th> </tr> </thead> <tbody> <tr> <td style="color: #161120;text-align: center;">13</td> <td style="font-weight: bold;;">1FA</td> </tr> <tr> <td style="color: #161120;text-align: center;">14</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">15</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">16</td> <td style="font-weight: bold;;">1FB</td> </tr> <tr> <td style="color: #161120;text-align: center;">17</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">18</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">19</td> <td style="font-weight: bold;;">1FD</td> </tr> <tr> <td style="color: #161120;text-align: center;">20</td> <td style=";">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">21</td> <td style=";">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">22</td> <td style="font-weight: bold;;">1FU16</td> </tr> <tr> <td style="color: #161120;text-align: center;">23</td> <td style=";">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">24</td> <td style=";">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">25</td> <td style="font-weight: bold;;">1GA</td> </tr> <tr> <td style="color: #161120;text-align: center;">26</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">27</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">28</td> <td style="font-weight: bold;;">1GB</td> </tr> <tr> <td style="color: #161120;text-align: center;">29</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">30</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">31</td> <td style="font-weight: bold;;">1GU16</td> </tr> <tr> <td style="color: #161120;text-align: center;">32</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">33</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">34</td> <td style="font-weight: bold;;">1MA</td> </tr> <tr> <td style="color: #161120;text-align: center;">35</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">36</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">37</td> <td style="font-weight: bold;;">1MB</td> </tr> <tr> <td style="color: #161120;text-align: center;">38</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">39</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">40</td> <td style="font-weight: bold;;">1MC</td> </tr> <tr> <td style="color: #161120;text-align: center;">41</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">42</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">43</td> <td style="font-weight: bold;;">1MD</td> </tr> <tr> <td style="color: #161120;text-align: center;">44</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">45</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">46</td> <td style="font-weight: bold;;">1RA</td> </tr> <tr> <td style="color: #161120;text-align: center;">47</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">48</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">49</td> <td style="font-weight: bold;;">1RB</td> </tr> <tr> <td style="color: #161120;text-align: center;">50</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">51</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">52</td> <td style="font-weight: bold;;">1RC</td> </tr> <tr> <td style="color: #161120;text-align: center;">53</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">54</td> <td style="text-align: center;;">2</td> </tr> <tr> <td style="color: #161120;text-align: center;">55</td> <td style="font-weight: bold;;">1RU16</td> </tr> <tr> <td style="color: #161120;text-align: center;">56</td> <td style="text-align: center;;">1</td> </tr> <tr> <td style="color: #161120;text-align: center;">57</td> <td style="text-align: center;;">2</td> </tr></tbody> </table>
Running your revised code, I get the following result for rws text:

xls.jpg


As you can see, it fails when category changes to 1GA.
Any thoughts? txs again for helping, appreciate it big time!
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG08Jul13
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] oLt
[COLOR="Navy"]Dim[/COLOR] oRt
[COLOR="Navy"]Dim[/COLOR] Rws [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Nam [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Dim[/COLOR] fRw [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] rng = Range("A:A").SpecialCells(xlCellTypeConstants, 1)
For Each oRng In rng.Areas '[COLOR="Green"][B]Do something with each sub range[/B][/COLOR]
'[COLOR="Green"][B]This returns column "A" Category Name[/B][/COLOR]
 Nam = oRng(1).Offset(-1)
 
'[COLOR="Green"][B]This code Creates all options Of Category Names , then[/B][/COLOR]
'[COLOR="Green"][B]test for "Nam", and roceeds if found.[/B][/COLOR]
ReDim Ray(1 To 20)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] oLt [COLOR="Navy"]In[/COLOR] Array("1F", "1G", "1M", "1R")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] oRt [COLOR="Navy"]In[/COLOR] Array("A", "B", "C", "D", "U16")
        [COLOR="Navy"]If[/COLOR] Nam = oLt & oRt [COLOR="Navy"]Then[/COLOR]
            Fd = True
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]If[/COLOR] Fd = True [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]Next[/COLOR] oRt
[COLOR="Navy"]Next[/COLOR] oLt
    [COLOR="Navy"]If[/COLOR] Fd = True [COLOR="Navy"]Then[/COLOR]
     
     Rws = Rws & oRng.Address & " Rows =  " & oRng.Count & Chr(10)
       
       For Each fRw In oRng '[COLOR="Green"][B] do something with each Row in sub range[/B][/COLOR]
            '[COLOR="Green"][B]MsgBox fRw.Address[/B][/COLOR]
                '[COLOR="Green"][B]Do something here[/B][/COLOR]
            
        [COLOR="Navy"]Next[/COLOR] fRw
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] oRng
MsgBox Rws
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
89
Dazzybeeguy
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,590
Messages
6,179,752
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top