Jim Rech's macro won't work (autofitting merged cells)

I cut and pasted Jim Rech's macro to autofit rows containing merged
cells into the worksheet code and it has no effect whatsoever. Neither
does putting it in the workbook code. This is Excel 2004 for the Mac,
running OS X. I REALLY need to be able to do this; can anybody help?

Thanks,
Ron M.


HI there,

Was wondering if anyone could help me. I have used a small macro to format the sheet to hide cells with no entries and then print. The problem is that when the sheet is protected, then macro wont work. The sheet needs to be protected by the end user. I am only really learning excel, does anyone out there have any suggestions that I could add to the following so the sheet can remain protected? Thankyou in advance!

	VB:
	
Intersect(ActiveSheet.UsedRange, Range("O:O")).Offset(0, 1).Select 
Range("O39:O155").Select 
Selection.EntireRow.Hidden = True 
Columns("O:O").Select 
Range("O21").Activate 
Selection.EntireColumn.Hidden = True 
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
Cells.Select 
Range("A21").Activate 
Selection.EntireRow.Hidden = False 
Columns("N:P").Select 
Range("N21").Activate 
Selection.EntireColumn.Hidden = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I got a code I found on this site to resize my cells as I type:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Application.EnableEvents = False
Rows(t.Row).EntireRow.AutoFit
Application.EnableEvents = True
End Sub

But it doesn't work on merged cells. Is there an additional code that can
be added to the above to include merged cells?

I know you can Autofit a single cell in a row, but is there a way to Autofit
merged cells in a row?
--
Jerri

I know you can Autofit a single cell in a row, but is there a way to Autofit
merged cells in a row?
--
Jerri

Hey again,

I was wondering if there is a way to use a macro to copy a merged cell without the blank spaces at the end? I'm attaching an example worksheet and I need to have the user copy the value of cell H3 so they can paste it in a program without the spaces at the end caused by the merged cells.

Thanks everyone!

we switched from operating system 2000 to xp and now my
macros wont work the security level is set to low but
they still dont work any ideas?

I have tried to use Jim Rech's macro to autofit a merged cell in excel.
However, I cannot seem to get this to work for my situation. I have (1) row
merged in (2) columns (D & E.) What am I doing wrong???

I have tried to use Jim Rech's macro to autofit a merged cell in excel.
However, I cannot seem to get this to work for my situation. I have (1) row
merged in (2) columns (D & E.) What am I doing wrong???

Hello everyone,

I've had a look at Jim Rech's excellent autofit macro. However for the purposes of what I'm doing I would like it to be able to decrease, as well as increase the height of the row. I've modified it accordingly and it worked fine.

My trouble began when I tried to debug it. It seems that I'm having issues whenever I copy-paste text, as the macro throws up an error and refuses to work properly. My code is as follows:


	VB:
	
 Range) 
     
    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single 
    Dim CurrCell As Range 
    Dim TargetWidth As Single, PossNewRowHeight As Single 
    Dim a As String 
     
    If Target.MergeCells Then 
         
         'assign a value to my test variable
        a = Cells(Target.Row, Target.Column).Value 
         
         'if target cell is not blank then....
        If a  "" Then 
            With Target.MergeArea 
                If .Rows.Count = 1 And .WrapText = True Then 
                    Application.ScreenUpdating = False 
                    CurrentRowHeight = .RowHeight 
                    TargetWidth = Target.ColumnWidth 
                     
                    For Each CurrCell In Selection 
                        MergedCellRgWidth = CurrCell.ColumnWidth + _ 
                        MergedCellRgWidth 
                    Next 
                    .MergeCells = False 
                    .Cells(1).ColumnWidth = MergedCellRgWidth 
                    .EntireRow.AutoFit 
                    PossNewRowHeight = .RowHeight 
                    .Cells(1).ColumnWidth = TargetWidth 
                    .MergeCells = True 
                    .RowHeight = PossNewRowHeight 
                End If 
            End With 
        End If 
         
         'if target cell is blank, reset the row height back to normal...
        If a = "" Then 
            Target.RowHeight = 15 
        End If 
         
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated. I think it has something to do with the .target usage, which is where I'm struggling.

Thanks in advance!

Vic.

I'm programmatically inserting some text into merged cells in a row. I have Wrap Text set and want the row height to expand as necessary to accommodate multiple lines of text. I was programmatically applying AutoFit once the cells had been filled but that didn't work. I subsequently found a Knowledge Base article saying the AutoFit doesn't work for merged cells! I can try to compute the row height required to accommodate the number of lines of wrapping text. But I don't really want to climb into calculating character widths etc. Any ideas gratefully appreciated.
Thanks,
David

Hi,

I know merged cells are awful, but I have to use them and I am using the
code below to autofit the merged cells in my protected sheet (protection
doesn't have a password). It's working great, except after data is entered
into the merged cells they are being reformatted as particially locked which
doesn't allow the user to go back to those cells if corrections are needed.
(I can see this because if I unprotect after data is entered and look at the
cell's format, the "locked" check box is not checked but completely filled
in). The only merged cell this is not happening to is the first cell on the
sheet. Can anyone find a solution for this? Thank you!!!!!!!!!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
Dim ProtectStatus As Boolean

With Target
If .MergeCells And .WrapText Then
ProtectStatus = Me.ProtectContents
If ProtectStatus Then Me.Unprotect ' password
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
On Error Resume Next
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
On Error GoTo 0
Application.ScreenUpdating = True
If ProtectStatus Then Me.Protect ' password
End If
End With
End Sub

Greetings,
Jim Rech's code works great for this, see:
http://groups.google.com/groups?thre...%40tkmsftngp05

But is it possible to trigger this with an event so that when the selection
is changed FROM the merged cell the code runs for the merged cell (ie would
be equivalent to BeforeSelectionChange)
Many Thanks

Hello,

I am new to the world of VBA and am having a problem.
I want to autofit rows that have merged cells in them.
I did take some code from another thread on the forum.
See below, my appologies for not referenceing the author I simply can not find where I got it from.

Also, I chose this one because it does it as you are in the cell rather than afterwards.

I receive an error stating:
"Unable to set the ColumnWidth property of the range class"

Does anyone have an Idea how to fix it?

Thank you, JIM

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
ActiveCell.Offset(-1, 0).Select
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

I have several problems I'm hoping someone can help me with. I've just created a spreadsheet that we will be using as a project review form. In this spreadsheet I have multiple rows of merged cells, column D to N, for comments. I need these comment areas, merged rows, to autofit whatever is cut and pasted or typed into them.

The first problem is cutting and pasting from a pdf. All of the documents we received for the projects we bid are in pdf format. We often cut and paste from those documents into an older version of our review sheet that was built in Word. Trying to cut and paste the same information into Excel ends up in failure. Is there a way that this can be done without jumping through hoops?

The second problem Iím running into is when I cut and paste into a comment area I get the typical Excel error message that the information being pasted does not fit the area it is being pasted into. It there a workaround for this?

My third problem is autofitting the pasted information into the merged cells. Iíve seen some VBA and tried a couple but they donít work automatically when the comments area is filled. How can I do this? I donít know VBA. I'm using MS Office 2003.

Leeanne

When a group of cells in a row are merged and the text wraps to two or more
lines, double-clicking the row border just to the left of column A (or
Format/Row/Autofit) auto-heights the row to one line of text. Either of
these actions should heighten the row to fit the text in the merged cells.

Hi,
I ran a search in the excel help forum and found similar posts but all suggestions I found referred to writing a vba code such as the one below:


	VB:
	
 Format() 
    Rows(x).entirerow.autofit 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have tried a vba code such as the one above but the issue persists.

I have a worksheet with merged cells [horizontal]; each cell is also formatted to warp text.
The issue is that the row size or cell size [vertical] does not adjust when the user types more text than what the cell size can handle.

would anyone have a suggestion?
cheers
Simon

I do all my work from gome where I have vista. I copy it onto my flash drive and take it to work but the crappy company computer wont reconize the file. I can get regular excel sheets to work but once macros and VBA are involved it wont work.
And yes I have tried saving the file as "macro enable spreadsheet".
Am I doing something wrong? I was told to store macros in "this workbook" and it would be fine. but apparently not.
Somebody please help!!

Hi all ,

i am pretty new to excel , but have been playing around with it for a while now, i am currently trying to record a macro to work on lets say cell A7

formula =RIGHT(A7,4) now i would like to apply this to another cell further down the A column, but it could be at any cell number, but the first will always be at cell A7, is this possible??

any advice / help would be greatly aprreciated.
Many Thanks.

steviegee

Can anyone please tell me why the following wont work?????

Private Sub Workbook_Open()
If Sheet1("B2") = "4" Then Application.Run ("MTEmailer")
End Sub

Sheet1 contains this:
- The formulas are in column B
1 Todays Date: =TODAY()
2 Todays Day: =WEEKDAY(B1,2) <--which is 4

The MTemailer macro itself works fine but it wont run based on the condition in B2... so simple yet it just wont do it for me.

Please help!!
Thanks in advance,
Tane

I'm trying to autofit merged cells when the enter key is hit. It seems excel moves the selected cell down one row when the enter key is hit and the code runs on the next cell versus the original cell. My code is below. Help!

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CurrentRowHeight As Single
Dim MergedCellRgWidth As Single
Dim CurrCell As Range
Dim RangeWidth As Single
Dim ActiveCellWidth As Single
Dim PossNewRowHeight As Single

If Target.MergeCells Then
ActiveCell.EntireRow.AutoFit
With Target.Resize(1, 1).MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
RangeWidth = .Width
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
While .Cells(1).Width < RangeWidth
.Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5
Wend
.Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, CurrentRowHeight, PossNewRowHeight)

End If
End With
End If
End Sub

Hi All,
Below is a sample excel format. I enabled merge cells from A1..A5 and B7..B10. I enabled autofilter and when I filter with column A(Cat), I can see only one value of the corresponding column B. How can I view all values of B when filtered with A? (Auto filter with merged cells) will work?

A B
Cat Val
21
22
a 23
24
25

12
34
b 56
30

Thankx

I am new to this forum, but have received a lot of help from other forums I've joined. I have a macro that was written for me that I need help editing. Or, a better way to do things.

What the marco does is it looks at the contents of a cell in Excel and then imports a JPG file with the same name as the value of the cell. When it imports the JPG it adjusts the size to fit the active cell. I would like to be able to merge cells together and have the macro fit the width of the merged cell keeping the height proportional. I have attached the file containing the macro. To start the macro you run PicForm. Of course, you will not have the folder assigned for the JPG files to reside.

OR! If I could replace this marco all together with a formula that would do the same thing - so it would automatically update if the merged cells if the value in the cell with the file name changes.

Can anyone help?

Thanks -

I have a macro to copy a merged cell from one sheet and paste it to another as a merged cell. Basically, I want to copy it AS IT IS and paste it AS IT IS. My code is below.
'Copy and paste the Procedure
    Sheets("Steps").Range("J2").Copy
    Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
I can manually do a copy and paste and it works fine, the pasted object stays as a merged cell. How can I do it in this macro?

Thanks in advance.

Hi I've just started using this software and can't find anywhere in help or google how to auto-fit the contents of merged cells. Like, if I have some text in a single cell then double click just below the row button thing it autofits the text but not when the text is spread over merged cells. Is there a solution? It's very annoying lol.