Merge and center based on cell value

[robertmwaring2]

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?

 

[mrshl9898]

"So, to be clear, if A8 <>"", i need to MERGE (not center across selection) columns G:I on that row (8)"


SO like this?

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

 

[robertmwaring2]

@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.

 

[mrshl9898]

Thanks for letting us know it worked out, and for the kind words. Enjoy yours too.