Borrar Mensaje appo-bits.com

  • Host: 188.190.124.15
  • IP: 188.190.124.15
  • Navegador: Netscape 5.x
  • S.O.: Windows NT
email   [email protected]
www  
Fecha  9/12/2012 03:35

JButton problem in Jpanel Java Programming Question
Mensaje
By WebOsPublisher

Merging and Unmerging Cells and macros to help with merging cells based
on selections by rows,by columns,by groups
Merge and Unmerge Cells
Merge and Unmerge Cells
Location: mvps.org/dmcritchie/excel/merge.htm
Code: mvps.org/dmcritchie/excel/code/merge.txt
Home page: mvps.org/dmcritchie/excel/excel.htm
<View>without Frames]
Copyright (C)1997 - 2002 F. David McRitchie
Introduction (#intro)
Merging several columns of cell in rows is frequently necessary to
design a spreadsheet form. The following macros are designed
to help merge and unmerge cells in a selection. The major
macro of importance would be MergeRxR below
and UnMergeSelected. Merging
here is referring to the cell container itself not the text within.
During the testing of these
macros some interesting observations were made on how cells are
colored if you chose a column and color it -- the merged cells are
not included in a column selection unless all cells in the merge
are selected.
Another observation, if you are merging cells you will want to use
cell borders (format, cells, borders), but if you unmerge cells you
will lose the border in the newly revealed separations.
Merge Macros (#macros)
The major macro here is MergeRxR which will
merge the cells for each selected area, but with a twist.
A normal cell merge would merge cells for rows and columns.
Code has been added to process each row within a selection
as if an additional selection. This will make the
selection process a lot faster as you need not worry about making
separate selections for each row.
MergeRxR_Join is a variation of MergeRxR that
will JOIN the contents
of the cells to be merged before merging.
MergeCxC is a variation of MergeRxR that merges
within the columns in the selected areas.
The macro UnMergeSelected.
is simply a macro for format, cells, alignment (tab), <off> Merge cells
The macro SetUpG20 is simply a quick setup for MarkCells
on the join.htm page.
This page contains some VBA macros. If you need assistance to install
or to use a macro please refer to Getting
Started with Macros. For more depth see
Install a Macro or User Defined Function
on my Formula page.Speed and efficiency considerations can be seen in
Proper, and other Text changes and in
Slow Response.
Merge Example (#example)
Color has been included to show selection of separate ranges.
The middle table shows the results of using MergeRxR macro so that
columns and not rows are merged. The table on right shows
the results of a standard Format, Cells, Alignment (tab), <x>Merge Cells
ABCDEF
1A1-1
B1-1C1-1
D1-1E1-1
F1-1
2A2B2C2D2
E2F2
3A3B3C3D3
E3F3
4A4-2
B4-2C4-3
D4-3E4-4
F4-4
5A5B5C5D5
E5F5
6A6B6-5
C6-5D6-6
E6-7F6-7
7A7B7C7
D7-6E7-7
F7-7
8A8-8
B8-8C8D8-6
E8F8
9A9-8
B9-8C9D9-6
E9-9F9-9
10A10-8
B10-8C10D10-6
E10-9F10-9
ABCDEF
1A1-1
2A2B2C2D2
E2F2
3A3B3C3D3
E3F3
4A4-2
C4-3
E4-4
5A5B5C5D5
E5F5
6A6
B6-5D6-6
E6-7
7A7B7C7
D7-6E7-7
8A8-8
C8D8-6E8F8
9A9-8
C9D9-6
E9-9
10A10-8
C10D10-6
E10-9
ABCDEF
1A1-1
2A2B2C2D2
E2F2
3A3B3C3D3
E3F3
4A4-2
C4-3
E4-4
5A5B5C5D5
E5F5
6A6
B6-5
D6-6
E6-7
7A7B7C7
8
A8-8C8E8
F8
9C9
E9-9
10C10
Merge Macros - Code (#code)
The macros code for the macros on this page can be found at
mvps.org/dmcritchie/excel/code/merge.txt
This page contains some VBA
macros. If you need assistance to install or to use a macro please
refer to my GetStarted page for
simpler instructions, or to my
GetFormula page
for more extensive information on macros.
The icon Merge Across is similar to the macro MergeRxR, but MergeRxR will
work with multiple selections which may make setting up a lot easier.
Option Explicit
'David McRitchie, mvps.org/dmcritchie/code/merge.txt 2002-05-31
Sub MergeRxR()
'-- Merge cells in multiple selected areas Row by Row ---
' limited to the usedrange (Ctrl+End)
' D.McRitchie, 2002-05-31 in merge.htm
Dim rng As Range
Dim rw As Range, ix As Long
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
MsgBox "nothing in usedrange to be merged"
GoTo done
End If
Dim i As Long, j As Long
For i = 1 To Selection.Areas.Count
For j = 1 To Selection.Areas(i).Rows.Count
Application.DisplayAlerts = False
Selection.Areas(i).Rows(j).MergeCells = True
Application.DisplayAlerts = True
Next
Next
done:
End Sub
Sub MergeRxR_Join()
'-- Merge cells in multiple selected areas Row by Row ---
' limited to the usedrange (Ctrl+End)
' JOIN contents of cells before merging
' D.McRitchie, 2002-05-31 in merge.htm
Dim Str As String, ii As Long
Str = ""
Dim rng As Range
Dim rw As Range, ix As Long
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
MsgBox "nothing in usedrange to be merged"
GoTo done
End If
Dim i As Long, j As Long
For i = 1 To Selection.Areas.Count
For j = 1 To Selection.Areas(i).Rows.Count
Application.DisplayAlerts = False
Str = "" '... concatenate cells for this row in selection
For ii = 1 To Selection.Areas(i).Rows(j).Columns.Count
Str = Str & " " & Selection.Areas(i).Rows(j).Columns(ii)
Next ii
Str = Mid(Str, 2)
Selection.Areas(i).Rows(j)(1) = Str
Selection.Areas(i).Rows(j).MergeCells = True
Application.DisplayAlerts = True
Next
Next
done:
End Sub
Sub MergeCxC()
'-- Merge cells in multiple selected areas Column by Column ---
' limited to the usedrange (Ctrl+End)
' D.McRitchie, 2002-05-31 in merge.htm
Dim rng As Range
Dim rw As Range, ix As Long
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
MsgBox "nothing in usedrange to be merged"
GoTo done
End If
Dim i As Long, j As Long
For i = 1 To Selection.Areas.Count
For j = 1 To Selection.Areas(i).Columns.Count
Application.DisplayAlerts = False
Selection.Areas(i).Columns(j).MergeCells = True
Application.DisplayAlerts = True
Next
Next
done:
End Sub
Sub UnMergeSelected()
'-- same as format, cells, alignment (tab), (turn off) Merge Cells
Selection.MergeCells = False
End Sub
Sub SetupG20()
Cells.MergeCells = False
Range("A1:G20").Select
Application.Run "personal.xls!MarkCells" 'see join.htm
End Sub
Additional comments on MergeRxR macro (#mergeRxR)
The used range is cell A1 through the last cell (Ctrl+End).
The used range can be identified as select A1 then extend with CTRL+SHIFT+END.
If you start with an empty sheet there is nothing in the used range.
This macro checks that the active cell is within the Used Range.
Forget the macro for a moment. You merge cells vertically the same
as merging horizontally. You can do both at the same time.
In Excel 2000 at least, if you select multiple areas with the help
of Ctrl each selection in the multiple selections will be processed
separately when you merge cells
Format, cells, Alignment (tab), <x> Merge Cells
If merge cells is greyed out, it means there is a mixture of
merged and unmerged cells, just hit it twice.
If merge D2:F40 you will get one big block of merged cells.
But if you wanted D2:F2, D3:F3, D4:F4, etc. to be merged
independently you could select those ranges independently
with help of Ctrl and then do your merge with the menus.
The macro MergeRxR (Merge Row by Row) is designed to
process merge cells in each selection per row, which is
what most forms would need.
MergeEmpty_A (#mergeempty_A)
Sub MergeEmpty_A()
'David McRitchie, 2004-05-27, programming
With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With
On Error GoTo done
Cells(Rows.Count, 2).End(xlUp).Activate
ActiveCell.Offset(0, -1).Activate
backthere:
Range(ActiveCell.Address, _
ActiveCell.End(xlUp).Address).Select
Selection.Merge
ActiveCell.Offset(-1, 0).Activate
GoTo backthere
done:
End Sub
A1B1C1D1
B2C2D2
B3C3D3
B4C4D4
A5B5C5D5
B6C6D6
B7C7D7
B8C8D8
B9C9D9
A10B10C10D10
B11C11D11
B12C12D12
A1B1C1D1
B2C2D2
B3C3D3
B4C4D4
A5B5C5D5
B6C6D6
B7C7D7
B8C8D8
B9C9D9
A10B10C10D10
B11C11D11
B12C12D12
Merge icons (#builtin)
Formatting icons (manually: Format, cells, alignment, horizontal and merge options)
-- Merge and Center -- customize, commands, format
-- Merge Across -- customize, commands, format
-- Merge Cells -- customize, commands, format
-- Unmerge Cells -- customize, commands, format
Merging of cells interfers with sorting, filtering, copying,
and pasting along with interferring with autofit of
row height and column widths (see "Center Across Selection" below and Related Area).
Problems with Merged Cells (#problems)
Can't sort merged cells
Realigning row height can be a problem. (see Jim Rech's solution in Related Area).
Alternatives to Merging Cells (#alternatives)
"Center Across Selection" (button was dropped in Excel 2000) available via
Format, cells, alignment, horizontal: Center Across Selection without the Merge option
is applied row by row on a selection and does not merge a block of cells.
On the other hand, "Merge and Center" (same button as was dropped from Excel 2000)
is a merge operation and will merge an entire block of cells into one entity
and does interfere with sorting etc.
Adjusting column widths may be an altertive to merged cells to avoid
"This operation requires the merged cells to be identically sized."
There are no builtin shortcut keys for merge/unmerge.
Related (#related)
The macros on this page are based on
my posting
of 2002-05-31 in misc.
Color Palette, the 56 palette colors in Excel
Rearranging Data in Columns contains macros
MarkCells and
MarkSepAreas
of interest in testing merging/unmerging columns. In fact
code form MarkSepAreas was used to help with MergeRxR described on this page.
Both Markcells and
MarkSepAreas were used for the
Example illustrations.
Row Height Adjustments for Merged Cells, Automatic", Jim Rech, programming, 1999/04/02
Text joining, separation, and reformatting with adjacent cells content, SepTerm
separates words after 1st word into cell to the right. SepLastTerm
separates the last word into the cell to the right. Not to be confused with merging and unmerging
cell borders (containers).
Sorting merged cells:
group:*excel* sort merged cells
Wrapping, not working with Merged Cells, AutoFitMergedCellRowHeight macro, Jim Rech, reposted 2002-08-09. Problems with automatic adjustment of row height involving cells with wrapped text when there are merged cells present, originally posted 1999-04-02.
2003-10-01
This page was introduced on May 31, 2002.
<My>Excel Pages -- home]
<INDEX>to my site and the off-site pages I reference]
<Site>Search -- Excel]
<Go>Back]
<Return>to TOP]
Please send your comments concerning this web page to: David McRitchie
send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved
                                                                                                                                                                                                                                                                                                                               



En respuesta a



Volver al foro

 

Responder


Nombre / Nick
EMail
Subject / Titulo
Link a tu Web
Recibir un email cuando mi mensaje sea contestado

Mensaje