Merge and center based on cell value

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
132
Office Version
  1. 365
Platform
  1. Windows
Hello again everyone, hate to be so annoying, but I really am trying. I have come across another issue I can't seem to resolve. I have a worksheet ("Production Sheet") that has 5 columns of information ranging from "A:J". The the first column, the information is centered across Selection from "A:D", the secong column is "E", the third is "F", the fourth "G:I", and the ifith "J". I have the following code to accomplish the formatting for the first Column ("A:D"):

Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A8:D" & lRow).Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
End With

Currently, the fourth column is not merged or centered, but I need it to be ONLY if the row in Coulmn A is not blank(no formulas in the cell). The data begins on Row 8 and will not have blanks in the middle of the range.
So, to be clear, if A8 <>"", i need to MERGE (not center across selection) columns G:I on that row (8)
and loop down for each row after that until if finds a blank cell in Column A - then stop.

I tried to alter the code above, but it merges the entire range from G8:G(whatever) together into one cell - which is close, but not what I need. Can anyone help me please?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
"So, to be clear, if A8 <>"", i need to MERGE (not center across selection) columns G:I on that row (8)"


SO like this?

Code:
Dim rownum As Long
Application.DisplayAlerts = False
rownum = 8
Do Until Cells(rownum, 1).Value = ""
Range("G" & rownum & ":I" & rownum).Merge
rownum = rownum + 1
Loop
Application.DisplayAlerts = True
 
Upvote 0
@mrshl9898
YES, thank you so very much. I am VERY new to VBA and am just getting to the point where I can almost understand what most code says, so reading this made perfect sense - though I would have never gotten there on my own. I am much better at formulas, and it kind of helps - but it's really not the same. I genuinely appreciate the assistance. Hope you have a great evening.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top