Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Merged cell autofill

When you have a column of data and you want to drag down an equation, how do you keep it from skipping rows?

Ex: Cells A1-A5 are merged. Cell(A1-A5)=Sheet1!A2, and I want cell (A6-A10) to yeild Sheet1!A3. Is that possible? I hate merged cells...


Post your answer or comment

comments powered by Disqus
Hi... To ALL...

I get one macro that insert picture from disk to selected cell, then auto resize that picture to merged cell size.

But, now i intend to use that same code to PASTE a picture (from System Clipboard) then resize to merged cell size

PASTING to selected merged cell, it works fine...
But.. The resize,... Not that fine.

Here is the code i intend to use;


	VB:
	
 CommandButton1_Click() 
    Dim p As Picture 
    Application.ScreenUpdating = False 
     
    Set p = ActiveSheet.Pictures.Paste 
     
    If Target.Cells.Count = 1 Then Set Target = Target.MergeArea 
    With Target 
        p.Top = .Top 
        p.Left = .Left 
        p.Width = .Width 
    End With 
     
End Sub 

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

I get ERROr in this line;

If Target.Cells.Count = 1 Then ...

Can someone help me in this...

THANKs

I don't know if this is possible but I have to sort a spreadsheet with new information every week and all the cells are related to each other and some of them are merged.here is the layout that repeats it self for 99 different groups of information that I want to keep together. How can I sort and be able to keep the merged cells intact? cell Layout.pdf

I'm looking for a good alternative to vertically merged cells. I know with horizontally merged cells, the alternative is to center across selection. How can I create a similar visual effect as vertically merged cells?

Hi. Can anybody tell me how can I copy the values from some merged cells and paste it at other empty cells just only the values and without have any blank cell in between each values?

The example as the picture below:
Capture.JPG

I have columns A through I that start at row 4 with varing merged cells. So the first merged row might be 3 rows deep or 5 or maybe just one. I would like to take each merged row and shift the values to one row so I can sort the spreadsheet.

Let's say I have merged cells A1 through A4 and the next column has unmerged cells B1 through B4 with each celling containing text. The two columns extend downward with column A containing differently sized merged cells. So while the first may be A1 through A4 the next may only be A5 through A6. What I need to do is look at column A and then merge the content of column B equal to that of column A. So if the first set in A is four deep the first set in column B is four deep. However, it must perserve all the content.

I hope some body here can give me some tips on how to solve my problem. It involves a sheet with merged cells I need to live with. I am trying to make a simple macro to introduce a formula that will multiply 3 previous cells (RC[-1]*RC[-3]*RC[-5]), however some times those previous cells are actually merged row cells. So if I use

	VB:
	
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-3]*RC[-5]" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If one of the cells is merged I will get a referenced cell without any value so my result is 0.

After serching on this forum among others I got the following code

	VB:
	
MergedDet = ActiveCell(1, 0).MergeArea 
MergedOcu = ActiveCell(1, -2).MergeArea 
MergedSev = ActiveCell(1, -4).MergeArea 
ActiveCell.Formula = "=" & MergedDet(1, 1) & "*" & MergedOcu(1, 1) & "*" & MergedSev(1, 1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However instead of placing the formula with the cell that will be multiplied like "=H9*F61*D9", it place the actual value to be multiplied on the formula "=8*3*3"

Any suggestions to the referenced cell instead of the value on the formula?

Crosstab Example.xls
Good morning all.

I am in the process of writing a macro to make a database export into Excel presentable for the printed medium. I know that this is not making best of of Excel, but it is how we have been instructed to use it by the top bods.

I have included an example spreadsheet for you to see.

My spreadsheet contains merged cells which are passed from the database and need to stay merged as they visually separate out the data. The issues is that the merged cells include one more column than the rest of the data. In order to rectify this I wanted the delete the first unused column in my dataset. This column will change depending on how much data has been exported.

When I do this manually I simply select the entire column and press delete. This works. However, when I try to do it programatically the Entire dataset is selected thanks to the merged cells.

My question is, how do I, having found the first unused column, select the entire column and delete it without selecting the entire dataset.

This is my code so far. Please note - the 'Select' part is simply so that I can test which data will be affected and will be removed once the code works.


	VB:
	
 Range 
 
Set nr4 = Range("IV1").End(xlToLeft).Offset(0,1) 'find first unused column
nr4.Select 'test to see which cell it has found
nr4.EntireColumn.Select 'test to ensure that just the column will be affected.
nr4.EntireCoulmn.Delete 'delete entire column

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for your time.

Hi,

I am sure this is simple for someone who knows much more than me on vba but I need a code to merge cells together.

I have a 4 x 2 block of cells to merge into one. A1:D2, E1:H2 and I1:L2 and then down the worksheet ie A3:D4, E3:H4 etc. All I want to do is end up with these cells merged into 4x2 blocks.

PS this is to allow a copy past into some stupid spreadsheet someone else has created with a load of merged cells!

I have some code to insert 3 columns after each column and 1 row after every row but if someone can add this to the start to make 1 macro that would be great

Thanks
Pete

Hi all, I have been using the following code to successfuly auto increase row height in (I know I know) merged cells. I have discovered that there is a maximum row height the cell wil grow, it seems to be 91.8. If double click the cell it shows the full contents, Ive decreased the font size, which helps, but but otherwise all I can view is the amont a cell 91.8 high will show.
Any ideas?

	VB:
	
 redomergerow() 
     '
     '
    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single 
    Dim CurrCell As Range 
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single 
    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) 
                Selection.Locked = False 
                Selection.FormulaHidden = False 
            End If 
        End With 
    End If 
     '
     
End Sub 

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


Hi Friends,

I am working no report which requires lots of merge cells for better layout.

I have 10 Columns say A-J. Coulmns A is business line (Say Food,Vegetable,Electric,Other - currently there are 4 but can increase in future). I would like to sort data using column A (in ascending order... Electric,Food,Other, Vegetable) and then sort columns B-J (which contains no of stores, order from each stores etc). I would then like to merge data in columns B-J if they are same.

I would like to do it for each business Line.

Thanks in advance
Backbencher

Dear Forum:Excel 2007 in Windows 7. I'm trying to put together a new schedule proposal to my managers. I have been trying for about 3 hours to do a couple of things with no success. Two questions:1) I have created a row of merged cells showing number of workers present in each hour of the day. The value in the merged pair of cells is the sum of the time in the two thinner columns above the merged cell. When I try to use this value in a graph, the graph takes two numbers from the merged cell. One is the desired data displayed in the merged cell, and the other value displayed in the graph is a zero- apparently the graph is treating the merged cell as two separate cells: the first cell containing the displayed value, and the other cell containing a ghost 0. The graphs look like a big saw tooth, dipping to zero between each value. How can I get the graph to look only at the intended displayed value in the merged cell? I've attached a graph showing the problem. 2) I want to put a light blue fill in the cells that represent the night time portion of the chart (24/7 full coverage schedule). The fill lays in front of the existing graphics, is opaque, and blocks vue of existing elements. I have not been able to "send to back". How can I do this?Much thanks, Kip.................

Hi All,

I need help sorting data which contains merged cells.

I have attached a file to this message which helps explains what I need.

In column B I have two rows of data (3-4) which needs to split into two columns of data with 1 row.

Any help would be greatly appreciated.

Cheers,

Maley

I am using Excel 2003. I would like to add some functionality to a spreadsheet I made that will allow rows to automatically be inserted as needed instead of just adding a bunch at the beginning and hoping it will be enough. I have searched around and found some useful information to get me started, but I am having a couple of problems.

First, I have tried using the "List" feature, but it does not work with merged cells. I found some code that does work (sort of) with merged cells but it only works after you press "tab" and the active cell stays on the same row. If you press "enter", a row is inserted but the format does not copy properly. I could fix this so enter would work, but then tab won't and ideally I want to make this as "dummy-proof" as possible. I think I know what needs to be done, just not how as I am not familiar with this VBA stuff.

Basically, I need to keep track of what row I start in, and then where the active cell is relative to it after a row is inserted, and based on that copy the format to the right place. Here is the code I have so far:


	VB:
	
 Excel.Range) 
    If Target.Column  2 Or Target.Count  1 Then Exit Sub 
    If Target.Row = Cells(65536, 2).End(xlUp).Row Then _ 
    Target.Offset(1, 0).EntireRow.Insert 
     
     '   Copy  formatting from the current row to the row below
    ActiveCell.Offset(0, 0).EntireRow.Copy 
    ActiveCell.Offset(1, 0).EntireRow.PasteSpecial xlPasteFormats 
    Application.CutCopyMode = False 
    ActiveCell.Offset(-1, 3).Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The other thing I noticed is that when I tab, that active cell ends up in the right place, but when you press enter, it shifts down one row (normal) but then to the right one row as well. It would be nice if it ended up where you would normally expect it regardless if you press enter or tab.

Anyway, am I on the right track or is there a way easier way to do this? Thanks in advance for your time!!

Hello, Iím not sure how to copy data from one cell on a different sheet into a merged cell on another. I tried naming the cells "ManagerNotes" and using that in my code but it wouldnít work. The range of the cells are B31:F44. My code looks something like this, and these are some of the things Iíve tried with no luck.

	VB:
	
[FONT=Calibri][SIZE=3]ws3.Range(" ao4").Resize(, 1).Copy[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]ws2.Cells(31, 2).PasteSpecial (xlPasteValues)[/SIZE][/FONT] 

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

	VB:
	
[FONT=Calibri][SIZE=3]ws3.Range(" ao4").Resize(, 1).Copy[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]ws2.Cells(ďManagerNotesĒ).PasteSpecial (xlPasteValues)[/SIZE][/FONT] 

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

	VB:
	
[FONT=Calibri][SIZE=3]ws3.Range(" ao4").Resize(, 1).Copy[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]ws2.Cells(B31:F44).PasteSpecial (xlPasteValues)[/SIZE][/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I know to avoid using ranged cells but it would be nice if i could get this working. Thanks for any help!

Hello,

I am trying to merge cells to make the words more legible but having much difficulty. I have multiple reports to do this with every month and looking for a formula/VBA solution. I have tried recording a cut and paste macro but that makes the cells identical even with different reports. I have also tried concatenating the cells but I need the solution in the original cells not adjacent ones. Sheet 1 is the original info after exporting and sheet 2 is the solution I am looking for. Help would be greatly appreciated.

Cole

The problem is to merge all the cells at the side of already merged cells, keeping all content and adding some text between the cells content "
" in this case
the atached example.xls file to this post shows 2 worksheets
on the first is the problem
on the second it is as it is suposed to be when the problem is solved
the list is extremly long, and manualy doing it is unthinkable
the number of cells to merge is unpredictable and changes constantly
so it needs to be as the merged cells on the side in order to mek just one row per name in order to import to DB
can someone do this?
I am not succeding in the task
I really need this macro (the colum titles I will need to adapt to my needs)
thanks for all help
Beste regards
John Pinheiro

I want to merge cell text from different column's and row's into a empty cell using VBA and array. e.g (fName, lName, address, City)
I have seen many way's to merge cell text from same row.

Best Regards
aeg

I would like help with accessing merged cells using a formula.

Take the following simple sheet. My data is in column A, and includes some merged cells. I place the formula INDIRECT("RC[-1]",0) in column B, and copy it down the sheet. I get the result as shown in column C, but I would like the result shown in column D!


	VB:
	
  |     A     |            B           |     C     |     D     | 
--|-----------|------------------------|-----------|-----------| 
1 | Bob       | =INDIRECT("RC[-1]",0)  | Bob       | Bob       | 
--|-----------|------------------------|-----------|-----------| 
2 | Harry     | =INDIRECT("RC[-1]",0)  | Harry     | Harry     | 
--|-----------|------------------------|-----------|-----------| 
3 |           | =INDIRECT("RC[-1]",0)  | Charles   | Charles   | 
  |           |------------------------|-----------|-----------| 
4 | Charles   | =INDIRECT("RC[-1]",0)  | 0         | Charles   | 
  |           |------------------------|-----------|-----------| 
5 |           | =INDIRECT("RC[-1]",0)  | 0         | Charles   | 
--|-----------|------------------------|-----------|-----------| 

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


I realise WHY this doesn't work (as Excel refers to the merged cells A3:A5 as just A3, and A4 & A5 don't essentially exist any more), but I would like to know if there's a way to get around it, and achieve the result in column D. Any help would be very much appreciated!

Thanks for looking,

Will

I searched and didn't find anything related to this topic so I hope someone can assist me. I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price

Hi all,

I have some spreadsheets which contains merged cells - unfortunately there are too many of them now to unmerge all the cells - so that solution is out of the equation.

Basically, my code searches for the value stored in the merged cell, it then needs to find all the values BELOW that merged cell. As .Find only returns the top-left figure/ref to that merged cell, I need some code to tell me how many of the cells are joined together at one time to form the 'merge' - does anyone have any ideas?

Cheers peeps

I did a search but could not find what I needed. This is probably pretty simple, but I'm just starting in Excel.

When I enter:
If Target.Address = "$H$1" Then
CalendarFrm.Show
End If
this into cell H1, everything works fine. Now, if I merge H1 with H2 and run the VBA code, nothing happens. In the spreadsheet, the merged cell still registers as H1, but the VBA code doesn't like the merged cell anymore. Is there a trick to referencing the merged cell?

I am getting run time error 1004 - cannot change part of merged cell.
what the code does is looks for the last entry in column B of In-Process sheet and locates that particular number in sheet Temp. then it copies everything after that entry into In-Process.
When I give a number, which is not in temp sheet, it gives me the no new records message. but however when I give the number present in the temp, it gives run time error. I have limited knowledge in VB so please help. thanks

PHP Code:
 Dim fnd As Range    Dim lr As Long    Dim i As Integer
    i = Sheets("In-Process").Range("B" & Rows.Count).End(xlUp).Value    lr = Range("A" & Rows.Count).End(xlUp).Row    Set fnd = Range("B:B").Find(i)            If Not fnd Is Nothing Then            Range(Cells(fnd.Row, 1), Cells(lr, 25)).Copy Sheets("In-Process").Range("A" & Rows.Count).End(xlUp)(2)        Else            MsgBox " No new records"        End If 


I have 17 cells merged (A-N) in a single row with text entered. I need to count the number of lines the wrapped text has in the merged cells. The count will be used to adjust the row height in a case statement.

Background information: several rows, below row 1, have data in columns A-N. The text in the merged cells provided information about the below cells.

As always, any help is greatly appreciated!!!


No luck finding an answer? You could always try Google.