VBA merging cells and counting Merged rows

Snaybot · #1 11-08-2015, 09:40 PM

I have a series of varying merged cells only in column A, such as a merged cell of 8 rows and then next merge cells is 4 cells and the next is 7 cells and so on and so on...

I Would like to figure out How to create a macro that instead of the 8 row merged cell it goes to 7 cells

So I would like the existing merged cells row count to go down by 1

so for 8 row merged cell it would go to a 7 row merged cell

so for 5 row merged cell it would go to a 4 row merged cell

so for 20 row merged cell it would go to a 19 row merged cell

Debaser · #2 11-09-2015, 03:02 AM

You could do this:

Code:

Sub resizeMerge()

    Dim lRow                  As Long
    Dim lCount                As Long
    Dim lLastRow              As Long

    lRow = 1

    ' change to your last row
    lLastRow = 25

    Do While lRow <= lLastRow
        With Cells(lRow, "A")
            If .MergeCells Then
                lCount = .MergeArea.Cells.Count
                .UnMerge
                If lCount > 2 Then .Resize(lCount - 1).Merge
            End If
        End With
        lRow = lRow + lCount
    Loop
End Sub

Snaybot · #3 11-09-2015, 03:56 AM

Thank you!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
counting cells to the right of criteria	Taras49	Excel	5	11-05-2015 11:52 AM
Table will not allow sorting because "cells are merged". I can't find the merged cells.	wendyloooo	Word Tables	1	05-26-2015 01:19 PM
Merging Rows - HELP please!	MessyJessy	Excel	7	01-14-2015 01:38 AM
Counting Color in Cells	Alaska1	Excel	7	07-28-2013 04:36 PM
merging rows and creating sub-rows	gib65	Excel	2	12-09-2011 02:09 PM