Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) < true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.

I'm not sure exactly what you are looking for, but the following example
would loop through the used range (filled range) of column A (one) looking at
each cell. It would call another macro if the current cell contains a bottom
border that is solid.

Does this help?

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
Run "MyMacro"
End If
Next rCell


"SteveDB1" wrote:

Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) < true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.

XP,
Thank you for the response.
This appears to work, but I've noticed something that caught me by surprise.
It appears to cut out, and stop at a certain point-- I'm assuming when it
finds a cell with no bottom border. Yet, when I go to check if the border
exists, it's there-- according to the page format window.




"XP" wrote:

I'm not sure exactly what you are looking for, but the following example
would loop through the used range (filled range) of column A (one) looking at
each cell. It would call another macro if the current cell contains a bottom
border that is solid.

Does this help?

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
Run "MyMacro"
End If
Next rCell


"SteveDB1" wrote:

Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) < true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.

Hi,

It is possible that something you are doing with the macro being called is
causing the code to interrupt and exit. Try running the code, but where you
call your other macro, temporarily replace that line with:

MsgBox rCell.Address

This will display a message box with the cell address everytime a cell
containing the border is found. If this works on its own (and it should),
then the problem lies in the program being called...in that case you may need
to post that code so it can be modified to work...

Hope this helps...


"SteveDB1" wrote:

XP,
Thank you for the response.
This appears to work, but I've noticed something that caught me by surprise.
It appears to cut out, and stop at a certain point-- I'm assuming when it
finds a cell with no bottom border. Yet, when I go to check if the border
exists, it's there-- according to the page format window.




"XP" wrote:

I'm not sure exactly what you are looking for, but the following example
would loop through the used range (filled range) of column A (one) looking at
each cell. It would call another macro if the current cell contains a bottom
border that is solid.

Does this help?

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
Run "MyMacro"
End If
Next rCell


"SteveDB1" wrote:

Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) < true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.

XP,
sure, anything's possible in my world.
I did as you said, and it shows the row locations of the borders. If a row
does not have a border all the way across the page, it ignores it, and only
lists rows with borders across the length of the row. I.e., in this case,
A$5:L$5, A$8:L$8, A$12:L$12, A$14:L$14, A$18:L$18, A$22:L$22, A$24:L$24,
A$26:L$26.
In my first run through, L24 did not have a border, so it skipped 24, and
went from 22 to 26.
Part of what was happening with my experience was that I'd activate the
code, it'd get to my macro, and ask me to select a range, it'd run through my
ranges, and then even if I was in the middle of the page, where boundaries
exist, it'd exit.
I'm starting to think now that the code was looking at another area than
what I was looking at, and did what it was told-- exit once you get to the
end-- even though it wasn't the end I was working on. That actually makes
sense as I consider it.







"XP" wrote:

Hi,

It is possible that something you are doing with the macro being called is
causing the code to interrupt and exit. Try running the code, but where you
call your other macro, temporarily replace that line with:

MsgBox rCell.Address

This will display a message box with the cell address everytime a cell
containing the border is found. If this works on its own (and it should),
then the problem lies in the program being called...in that case you may need
to post that code so it can be modified to work...

Hope this helps...


"SteveDB1" wrote:

XP,
Thank you for the response.
This appears to work, but I've noticed something that caught me by surprise.
It appears to cut out, and stop at a certain point-- I'm assuming when it
finds a cell with no bottom border. Yet, when I go to check if the border
exists, it's there-- according to the page format window.




"XP" wrote:

I'm not sure exactly what you are looking for, but the following example
would loop through the used range (filled range) of column A (one) looking at
each cell. It would call another macro if the current cell contains a bottom
border that is solid.

Does this help?

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
Run "MyMacro"
End If
Next rCell


"SteveDB1" wrote:

Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) < true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.

Yeah, the code I gave you is not designed for interaction; it is designed to
just run through everything.

Another approach might be to select your range in advance and then change
the code to only run on the selected cells like so:

Dim rCell As Range
For Each rCell In Selection
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
MsgBox rCell.Row
End If
Next rCell

This way everything you select is processed...

"SteveDB1" wrote:

XP,
sure, anything's possible in my world.
I did as you said, and it shows the row locations of the borders. If a row
does not have a border all the way across the page, it ignores it, and only
lists rows with borders across the length of the row. I.e., in this case,
A$5:L$5, A$8:L$8, A$12:L$12, A$14:L$14, A$18:L$18, A$22:L$22, A$24:L$24,
A$26:L$26.
In my first run through, L24 did not have a border, so it skipped 24, and
went from 22 to 26.
Part of what was happening with my experience was that I'd activate the
code, it'd get to my macro, and ask me to select a range, it'd run through my
ranges, and then even if I was in the middle of the page, where boundaries
exist, it'd exit.
I'm starting to think now that the code was looking at another area than
what I was looking at, and did what it was told-- exit once you get to the
end-- even though it wasn't the end I was working on. That actually makes
sense as I consider it.







"XP" wrote:

Hi,

It is possible that something you are doing with the macro being called is
causing the code to interrupt and exit. Try running the code, but where you
call your other macro, temporarily replace that line with:

MsgBox rCell.Address

This will display a message box with the cell address everytime a cell
containing the border is found. If this works on its own (and it should),
then the problem lies in the program being called...in that case you may need
to post that code so it can be modified to work...

Hope this helps...


"SteveDB1" wrote:

XP,
Thank you for the response.
This appears to work, but I've noticed something that caught me by surprise.
It appears to cut out, and stop at a certain point-- I'm assuming when it
finds a cell with no bottom border. Yet, when I go to check if the border
exists, it's there-- according to the page format window.




"XP" wrote:

I'm not sure exactly what you are looking for, but the following example
would loop through the used range (filled range) of column A (one) looking at
each cell. It would call another macro if the current cell contains a bottom
border that is solid.

Does this help?

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
Run "MyMacro"
End If
Next rCell


"SteveDB1" wrote:

Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) < true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.

What would be involved in iterating through successive columns?
As I look at your code, it seems that I'd need to use an outer for-next
loop, and change the .columns(1) to .columns(i)
i.e.,

for each rcell.activesheet.usedrange.columns(i).rows

for each rcell.activesheet.usedrange.columns(1).rows
if rcell.borders(xledgebottom).linestyle = xlsolid then
run "mymacro"
end if
next rcell
next rcell

would this be correct?
Again, thank you for your help.


"XP" wrote:

Hi,

It is possible that something you are doing with the macro being called is
causing the code to interrupt and exit. Try running the code, but where you
call your other macro, temporarily replace that line with:

MsgBox rCell.Address

This will display a message box with the cell address everytime a cell
containing the border is found. If this works on its own (and it should),
then the problem lies in the program being called...in that case you may need
to post that code so it can be modified to work...

Hope this helps...


"SteveDB1" wrote:

XP,
Thank you for the response.
This appears to work, but I've noticed something that caught me by surprise.
It appears to cut out, and stop at a certain point-- I'm assuming when it
finds a cell with no bottom border. Yet, when I go to check if the border
exists, it's there-- according to the page format window.




"XP" wrote:

I'm not sure exactly what you are looking for, but the following example
would loop through the used range (filled range) of column A (one) looking at
each cell. It would call another macro if the current cell contains a bottom
border that is solid.

Does this help?

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
Run "MyMacro"
End If
Next rCell


"SteveDB1" wrote:

Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) < true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.

Drawing on my last example using SELECTION, I would code it as follows,
although there are several ways to do this, and someone else may post
something more elegant, but this works.

In this case, you would select your initial cells to be processed, BUT only
in the first column. The code shifts the selection to the right, in this
case, one column at a time for three columns and runs through the same
procedure. So make your initial cell selection in one column (do not select
the entire column or it will run all the way down the sheet and it will take
awhile!) and run it:

Dim rCell As Range
Dim lX As Long
Do
For Each rCell In Selection
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
MsgBox rCell.Address
End If
Next rCell
lX = lX + 1
Selection.Offset(0, 1).Select
Loop Until lX = 3

With the message box in there, you can see what it's doing and step through.
In fact, the borders can be in different cells in each column, but it finds
them.

I'm going home for today, but I will check for your post tomorrow, in case
you post back with another question.

Regards



"SteveDB1" wrote:

What would be involved in iterating through successive columns?
As I look at your code, it seems that I'd need to use an outer for-next
loop, and change the .columns(1) to .columns(i)
i.e.,

for each rcell.activesheet.usedrange.columns(i).rows

for each rcell.activesheet.usedrange.columns(1).rows
if rcell.borders(xledgebottom).linestyle = xlsolid then
run "mymacro"
end if
next rcell
next rcell

would this be correct?
Again, thank you for your help.


"XP" wrote:

Hi,

It is possible that something you are doing with the macro being called is
causing the code to interrupt and exit. Try running the code, but where you
call your other macro, temporarily replace that line with:

MsgBox rCell.Address

This will display a message box with the cell address everytime a cell
containing the border is found. If this works on its own (and it should),
then the problem lies in the program being called...in that case you may need
to post that code so it can be modified to work...

Hope this helps...


"SteveDB1" wrote:

XP,
Thank you for the response.
This appears to work, but I've noticed something that caught me by surprise.
It appears to cut out, and stop at a certain point-- I'm assuming when it
finds a cell with no bottom border. Yet, when I go to check if the border
exists, it's there-- according to the page format window.




"XP" wrote:

I'm not sure exactly what you are looking for, but the following example
would loop through the used range (filled range) of column A (one) looking at
each cell. It would call another macro if the current cell contains a bottom
border that is solid.

Does this help?

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.Borders(xlEdgeBottom).LineStyle = xlSolid Then
Run "MyMacro"
End If
Next rCell


"SteveDB1" wrote:

Hi all.
On one of my macros, I want to set it up to run through a single sheet until
it finds the last row of contents.
However, I want it to only do select cells, that are bounded by borders.
I.e., instead of my having to activate for each cell group, I'd like to

I thought something like this would do it, but I'm missing something.
dim i as integer
dim myRng as range


for i = 1 to lastcell ' where lastcell is the last row with data.

set myRng = nothing

set myRng = ?

if myRng.Border(xlEdgeBottom) < true then

Run "MyMacro"

Else : myRng.cells(?) = offset (1,0) 'down one row, zero columns

end if

next i

I'm sure something needs to be stated differently, I just haven't figured
out what, yet.
Thank you.
Best.