Resizing a merged range
 

I currently have a block of cells that I've merged to create a big "Comments"
section on a worksheet. I now need to expand that block (by, let's say 10
rows) and then reestablish the print area (of the entire worksheet) to fit on
1 page. I'll get to the printing part later but currently, I need a tip on
how to select multiple cells (like the click-and-drag mouse method)
programmatically.

I have the following code/pseudo-code:

'Find the Comments section
Cells.Find(What:="Comments").Select 'find the header
ActiveCell.Offset(1, 0).Select 'go down to the next
row/cell
Selection.Clear 'unmerge cells
'*********here's where I get lost************
ActiveCell.Offset(10, 0).Select 'select next 10
rows/cells
Remerge the cells (hopefully the # of columns remains the same)

So, I need to keep selecting cells (in a loop?) and then merge them.

Any pointers are greatly appreciated.

Resizing a merged range
 

'*********here's where I get lost************

Me too. You want to select 10 cells down a column starting with the active
cell?

ActiveCell.Resize(10).Select

Remerge the cells

What does "remerge" mean?

--
Jim
"ReportSmith" wrote in message
...
I currently have a block of cells that I've merged to create a big
"Comments"
section on a worksheet. I now need to expand that block (by, let's say 10
rows) and then reestablish the print area (of the entire worksheet) to fit
on
1 page. I'll get to the printing part later but currently, I need a tip
on
how to select multiple cells (like the click-and-drag mouse method)
programmatically.

I have the following code/pseudo-code:

'Find the Comments section
Cells.Find(What:="Comments").Select 'find the header
ActiveCell.Offset(1, 0).Select 'go down to the
next
row/cell
Selection.Clear 'unmerge
cells
'*********here's where I get lost************
ActiveCell.Offset(10, 0).Select 'select next 10
rows/cells
Remerge the cells (hopefully the # of columns remains the same)

So, I need to keep selecting cells (in a loop?) and then merge them.

Any pointers are greatly appreciated.

Resizing a merged range
 

I'm not exactly sure what you want, so this is purely a guess

Sub Test()

'Find the Comments section
Cells.Find(What:="Comments").Select 'find the header
ActiveCell.Offset(1, 0).Select 'go down to the
nextrow/cell
Selection.MergeCells = False
'unmerge cells
'*********here's where I get lost************
ActiveCell.Resize(10, 1).MergeCells = True 'select
next 10rows/cells


End Sub


"ReportSmith" wrote:

I currently have a block of cells that I've merged to create a big "Comments"
section on a worksheet. I now need to expand that block (by, let's say 10
rows) and then reestablish the print area (of the entire worksheet) to fit on
1 page. I'll get to the printing part later but currently, I need a tip on
how to select multiple cells (like the click-and-drag mouse method)
programmatically.

I have the following code/pseudo-code:

'Find the Comments section
Cells.Find(What:="Comments").Select 'find the header
ActiveCell.Offset(1, 0).Select 'go down to the next
row/cell
Selection.Clear 'unmerge cells
'*********here's where I get lost************
ActiveCell.Offset(10, 0).Select 'select next 10
rows/cells
Remerge the cells (hopefully the # of columns remains the same)

So, I need to keep selecting cells (in a loop?) and then merge them.

Any pointers are greatly appreciated.

Resizing a merged range
 

Yes.

"Jim Rech" wrote:

'*********here's where I get lost************

Me too. You want to select 10 cells down a column starting with the active
cell?

ActiveCell.Resize(10).Select

Remerge the cells

What does "remerge" mean?

--
Jim
"ReportSmith" wrote in message
...
I currently have a block of cells that I've merged to create a big
"Comments"
section on a worksheet. I now need to expand that block (by, let's say 10
rows) and then reestablish the print area (of the entire worksheet) to fit
on
1 page. I'll get to the printing part later but currently, I need a tip
on
how to select multiple cells (like the click-and-drag mouse method)
programmatically.

I have the following code/pseudo-code:

'Find the Comments section
Cells.Find(What:="Comments").Select 'find the header
ActiveCell.Offset(1, 0).Select 'go down to the
next
row/cell
Selection.Clear 'unmerge
cells
'*********here's where I get lost************
ActiveCell.Offset(10, 0).Select 'select next 10
rows/cells
Remerge the cells (hopefully the # of columns remains the same)

So, I need to keep selecting cells (in a loop?) and then merge them.

Any pointers are greatly appreciated.

Resizing a merged range
 

Remerge = just merge the cells again (after selecting the extra cells)

"Jim Rech" wrote:

'*********here's where I get lost************

Me too. You want to select 10 cells down a column starting with the active
cell?

ActiveCell.Resize(10).Select

Remerge the cells

What does "remerge" mean?

--
Jim
"ReportSmith" wrote in message
...
I currently have a block of cells that I've merged to create a big
"Comments"
section on a worksheet. I now need to expand that block (by, let's say 10
rows) and then reestablish the print area (of the entire worksheet) to fit
on
1 page. I'll get to the printing part later but currently, I need a tip
on
how to select multiple cells (like the click-and-drag mouse method)
programmatically.

I have the following code/pseudo-code:

'Find the Comments section
Cells.Find(What:="Comments").Select 'find the header
ActiveCell.Offset(1, 0).Select 'go down to the
next
row/cell
Selection.Clear 'unmerge
cells
'*********here's where I get lost************
ActiveCell.Offset(10, 0).Select 'select next 10
rows/cells
Remerge the cells (hopefully the # of columns remains the same)

So, I need to keep selecting cells (in a loop?) and then merge them.

Any pointers are greatly appreciated.

Resizing a merged range
 

Barb,
Good guess...Thank you. With some additional tweaking, I got the code to do
what I wanted. Now it's on to the next step.

Thanks again.

"Barb Reinhardt" wrote:

I'm not exactly sure what you want, so this is purely a guess

Sub Test()

'Find the Comments section
Cells.Find(What:="Comments").Select 'find the header
ActiveCell.Offset(1, 0).Select 'go down to the
nextrow/cell
Selection.MergeCells = False
'unmerge cells
'*********here's where I get lost************
ActiveCell.Resize(10, 1).MergeCells = True 'select
next 10rows/cells


End Sub


"ReportSmith" wrote:

I currently have a block of cells that I've merged to create a big "Comments"
section on a worksheet. I now need to expand that block (by, let's say 10
rows) and then reestablish the print area (of the entire worksheet) to fit on
1 page. I'll get to the printing part later but currently, I need a tip on
how to select multiple cells (like the click-and-drag mouse method)
programmatically.

I have the following code/pseudo-code:

'Find the Comments section
Cells.Find(What:="Comments").Select 'find the header
ActiveCell.Offset(1, 0).Select 'go down to the next
row/cell
Selection.Clear 'unmerge cells
'*********here's where I get lost************
ActiveCell.Offset(10, 0).Select 'select next 10
rows/cells
Remerge the cells (hopefully the # of columns remains the same)

So, I need to keep selecting cells (in a loop?) and then merge them.

Any pointers are greatly appreciated.