Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Select Range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Select Range
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to select range | Excel Discussion (Misc queries) | |||
Select a range in code | Excel Programming | |||
select range from code | Excel Programming | |||
How do I select this range using code....another try at it. | Excel Programming | |||
How do I select this range using code? | Excel Programming |