Macro to Merge cells A - D if cell B is blank in user defined selection

**MagicMan** · 07-21-2023, 11:26 AM

Hi Team,

I'm trying to work our a macro that, within a user defined selection, will identify whether there is a value in cell B.

If NO value is present in cell B then the macro should merge cells B to D of the row and then go to next row and start again.

Basically,

The user simply

selects a range (could be a range (e.g. A5:D22) or a number of rows)
activates the macro

A range (greater than a single cell) MUST be selected. If no range is selected then an error message is displayed.

The "Desired" tab in the attached indicates the outcome (on the original tab - a new tab is not required) assuming only Range A5:D22 is selected.

Thanks all

MM

**Marc L** · 07-21-2023, 11:40 AM

Hi,

if it is always for columns A:D so in order to simplify why not just selecting only column B cells ?

**MagicMan** · 07-21-2023, 11:58 AM

Hi Marc,

That works too. Yes, always Cols A to D where B is empty

I'm pretty relaxed on what actually needs to be selected, just as long as it only operates on the user's selection

**Marc L** · 07-21-2023, 12:14 PM

Try this VBA demonstration only with a contiguous column B selection :

PHP Code:


Sub Demo1() 
        Dim Rc As Range 
        If Selection.Column <> 2 Or Selection.Columns.Count > 1 Or Selection.Areas.Count > 1 Then Beep: Exit Sub 
    If Application.CountBlank(Selection) Then 
        For Each Rc In Selection.SpecialCells(4):  Rc(1, 0).Resize(, 4).Merge:  Next 
        Selection.Select 
    End If 
End Sub

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

**MagicMan** · 07-21-2023, 12:38 PM

Marc L

Perfect, did the job bang on!

(Rep added)

MM

Macro to Merge cells A - D if cell B is blank in user defined selection

LinkBack

Thread Tools

Rate This Thread

Display

Macro to Merge cells A - D if cell B is blank in user defined selection

Re: Macro to Merge cells A - D if cell B is blank in user defined selection

Re: Macro to Merge cells A - D if cell B is blank in user defined selection

Re: Macro to Merge cells A - D if cell B is blank in user defined selection

Re: Macro to Merge cells A - D if cell B is blank in user defined selection

Thread Information

Users Browsing this Thread

Similar Threads

data entry to defined cells in a table from a seperate user input selection section

How to move cell contents using a user-defined macro

[SOLVED] user defined selection box

Merge several cells into one cell separated by a defined character

comparing user defined cell to other cells using VLOOKUP

all user defined cell format del using macro

all user defined cell format del using macro

Bookmarks

Bookmarks

Posting Permissions