Hello all! I have a spreadsheet with two sections that I need to format with
borders around each section. I accomplished this on the bottom section, by
doing this:
Cells. Find(What="Customer
Hold",After:=ActiveCell,LookIn:=xlFormulas,_LookAt :=xlPart,SearchOder:=xlByRows,Search Direction:=xlNext,_MatchCase=False).Activate
ActiveCell.Resize(15,9).Select

Then I have the code to add the borders to that section.

For the top section, however, the rows are not static (i.e. today it goes to
row 40, tomorrow might go to row 65), and I don't need to go clear to the
last row used, because that would select the last row of the second section.

Is there a way to find "Customer Hold" again and write a code that would
select from A1:J? where J? = 4 rows prior to "Customer Hold" (Section one
always ends 4 rows prior to Customer Hold, because earlier in the code I find
the section containing Customer Hold, cut it, and have code written to find
the last row with data in it in column A, then paste the Customer Hold
section 4 rows below there.), or conversely, backwards from 4 rows above
Customer Hold up to A1 including to column J?

Somewhere in my struggles I must be dancing all around writing it correctly,
I just can't get the syntax.

Any suggestions would be most appreciated.
--
Best Regards,

Beth

Thanks for the tip. Would this work? It seemed to work this time, but don't
know if it will if/when the used cells increase/decrease.

Sub Region()

Worksheets("Sheet1").Activate
Range("A1").CurrentRegion.Select

End Sub




--
Best Regards,

Beth


"Jim Thomlinson" wrote:

Try something like this...

dim rngFoundCustomer as range

set rngFoundCustomer = Cells. Find(What="Customer
Hold",After:=ActiveCell,LookIn:=xlFormulas,_LookAt :=xlPart,SearchOder:=xlByRows,Search Direction:=xlNext,_MatchCase=False)

rngFoundCustomer.Resize(15,9).Select

'Set your border style here

Activesheet.Range("A1:J" & rngFoundCustomer - 4).Select

'Set your border style here

set rngFoundCustomer = Nothing
--
HTH...

Jim Thomlinson


"BethB" wrote:

Hello all! I have a spreadsheet with two sections that I need to format with
borders around each section. I accomplished this on the bottom section, by
doing this:
Cells. Find(What="Customer
Hold",After:=ActiveCell,LookIn:=xlFormulas,_LookAt :=xlPart,SearchOder:=xlByRows,Search Direction:=xlNext,_MatchCase=False).Activate
ActiveCell.Resize(15,9).Select

Then I have the code to add the borders to that section.

For the top section, however, the rows are not static (i.e. today it goes to
row 40, tomorrow might go to row 65), and I don't need to go clear to the
last row used, because that would select the last row of the second section.

Is there a way to find "Customer Hold" again and write a code that would
select from A1:J? where J? = 4 rows prior to "Customer Hold" (Section one
always ends 4 rows prior to Customer Hold, because earlier in the code I find
the section containing Customer Hold, cut it, and have code written to find
the last row with data in it in column A, then paste the Customer Hold
section 4 rows below there.), or conversely, backwards from 4 rows above
Customer Hold up to A1 including to column J?

Somewhere in my struggles I must be dancing all around writing it correctly,
I just can't get the syntax.

Any suggestions would be most appreciated.
--
Best Regards,

Beth

Try something like this...

dim rngFoundCustomer as range

set rngFoundCustomer = Cells. Find(What="Customer
Hold",After:=ActiveCell,LookIn:=xlFormulas,_LookAt :=xlPart,SearchOder:=xlByRows,Search Direction:=xlNext,_MatchCase=False)

rngFoundCustomer.Resize(15,9).Select

'Set your border style here

Activesheet.Range("A1:J" & rngFoundCustomer - 4).Select

'Set your border style here

set rngFoundCustomer = Nothing
--
HTH...

Jim Thomlinson


"BethB" wrote:

Hello all! I have a spreadsheet with two sections that I need to format with
borders around each section. I accomplished this on the bottom section, by
doing this:
Cells. Find(What="Customer
Hold",After:=ActiveCell,LookIn:=xlFormulas,_LookAt :=xlPart,SearchOder:=xlByRows,Search Direction:=xlNext,_MatchCase=False).Activate
ActiveCell.Resize(15,9).Select

Then I have the code to add the borders to that section.

For the top section, however, the rows are not static (i.e. today it goes to
row 40, tomorrow might go to row 65), and I don't need to go clear to the
last row used, because that would select the last row of the second section.

Is there a way to find "Customer Hold" again and write a code that would
select from A1:J? where J? = 4 rows prior to "Customer Hold" (Section one
always ends 4 rows prior to Customer Hold, because earlier in the code I find
the section containing Customer Hold, cut it, and have code written to find
the last row with data in it in column A, then paste the Customer Hold
section 4 rows below there.), or conversely, backwards from 4 rows above
Customer Hold up to A1 including to column J?

Somewhere in my struggles I must be dancing all around writing it correctly,
I just can't get the syntax.

Any suggestions would be most appreciated.
--
Best Regards,

Beth

Depending what you want I would guess that that solution would be hit and
miss. Give it a try though. You might be pleasantly surprised. My solution
basically creates a place holder (range object which is the same kind of
thing as the activecell) which we just refer back to after we have formatted
the borders. It should work no matter how many rows you have...
--
HTH...

Jim Thomlinson


"BethB" wrote:

Thanks for the tip. Would this work? It seemed to work this time, but don't
know if it will if/when the used cells increase/decrease.

Sub Region()

Worksheets("Sheet1").Activate
Range("A1").CurrentRegion.Select

End Sub




--
Best Regards,

Beth


"Jim Thomlinson" wrote:

Try something like this...

dim rngFoundCustomer as range

set rngFoundCustomer = Cells. Find(What="Customer
Hold",After:=ActiveCell,LookIn:=xlFormulas,_LookAt :=xlPart,SearchOder:=xlByRows,Search Direction:=xlNext,_MatchCase=False)

rngFoundCustomer.Resize(15,9).Select

'Set your border style here

Activesheet.Range("A1:J" & rngFoundCustomer - 4).Select

'Set your border style here

set rngFoundCustomer = Nothing
--
HTH...

Jim Thomlinson


"BethB" wrote:

Hello all! I have a spreadsheet with two sections that I need to format with
borders around each section. I accomplished this on the bottom section, by
doing this:
Cells. Find(What="Customer
Hold",After:=ActiveCell,LookIn:=xlFormulas,_LookAt :=xlPart,SearchOder:=xlByRows,Search Direction:=xlNext,_MatchCase=False).Activate
ActiveCell.Resize(15,9).Select

Then I have the code to add the borders to that section.

For the top section, however, the rows are not static (i.e. today it goes to
row 40, tomorrow might go to row 65), and I don't need to go clear to the
last row used, because that would select the last row of the second section.

Is there a way to find "Customer Hold" again and write a code that would
select from A1:J? where J? = 4 rows prior to "Customer Hold" (Section one
always ends 4 rows prior to Customer Hold, because earlier in the code I find
the section containing Customer Hold, cut it, and have code written to find
the last row with data in it in column A, then paste the Customer Hold
section 4 rows below there.), or conversely, backwards from 4 rows above
Customer Hold up to A1 including to column J?

Somewhere in my struggles I must be dancing all around writing it correctly,
I just can't get the syntax.

Any suggestions would be most appreciated.
--
Best Regards,

Beth