[RESOLVED] Merge blank cells Macro in Excel

[Jbing]

I am wondering if there is a way to write a macro in Excel to where it searches from say cell A13 to C2000 and merges all cells which are blank with the cells to the left of them.

Any and all help is welcome!

Thanks!

[emartinho]

Check out the Range.Merge method. It merges the specified ranges into 1 cell.
e.g.

Range("a1:B2").Merge ' merge all the cells in the range into 1 cell
Range("a1:B2").Merge True 'the optional parameter merges cells of each row separately.

-EM

[Jbing]

I am guessing that this will merge the cells whether there is data in them or not. That is a good start although I don't know what the ranges are going to be. I will have lots of data throughout the range of A13 to C2000 and will only want to merge the cells that are blank with the cells that are to the left of them. So maybe if there is a way to search for the ranges of blank cells and then use the Range("a1:B2").Merge True.

[westconn1]

column A has no cells to the left

for each mycell in range("B13:c2000")
   if isempty(mycell) then mycell.offset(,-1).resize(,2).merge
next

not tested and does not check if the cell to the left of the empty cell is also empty

[Jbing]

This is perfect! This is exactly what I was looking for! Thank you!

It actually ended up being the following but even I could figure out that little change.

for each mycell in range("B13:c2000")
if isempty(mycell) then mycell.offset(,0).resize(,2).merge
next

[westconn1]

so you merge with the cell to the right?
in that case you may need to use range("a13:c2000")
you can omit the offset altogether
if isempty(mycell) then mycell.resize(,2).merge

[Shireen_az]

Is there a way to fill cells to the right of the column with data from the left and not merge them?

[westconn1]

if isempty(mycell) then mycell = mycell.offset(,-1)

[gauravvarshney]

Can you please help me with same sort of issue.
Where I have to merge the blank cells below with the cell above having the data.
For eg....
Data
Blank
Blank
Blank

all the 4 cells should be merged
and data should be taken from the 1st cell

[westconn1]

try like

For i = 2 To Cells(65535, 1).End(xlUp).Row
    If IsEmpty(Cells(i, 1)) Then Range(Cells(i - 1, 1), Cells(i, 1)).Merge
Next