ExcelBanter - Merge Macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Merge Macro (https://www.excelbanter.com/excel-discussion-misc-queries/172636-merge-macro.html)

Sub Macro6()
Dim row1 As Long
Dim col1 As Long

Set MYrange = Application.InputBox(Prompt:="Please select
range", _
Title:="Tihs Siht Kcuf", Type:=8)
row1 = MYrange.Row
col1 = MYrange.Column

Range(MYrange, Cells(row1, col1 - 1 + Range("P" &
row1))).Select
Selection.Merge
Selection = Range("P" & row1) & "%"
Selection.Interior.ColorIndex = 35

I have been working on this all night and this is the best I can come
up with. I need to merge cells based on what is in P, Q, R, S, and T

So for instance IF Range("P" row1) <0 then
Starting at Range("AA" row1) I want the cells to
merge for the number that is in "P". So if the number is 10 then it
woudl merge cells AA:AJ. Then merge the cells to the right based on
what is in Q. The tricky part is that any of these at anytime can be
0 and I can not think of how to do this.

Thanks,
Jay

Here is all of my code.

Set myrange2 = Cells(row1, col1 + Range("P" & row1))
Range(myrange2, Cells(row1, col1 - 1 + Range("Q" &
row1) + Range("P" & row1))).Select
Selection.Merge
Selection = Range("Q" & row1) & "%"

Range(Cells(row1, col1 + Range("p" & row1) + Range("Q" & row1)),
Cells(row1, _
col1 + Range("P" & row1) + Range("Q" & row1) + Range("R" &
row1))).Select
Selection.Merge
Selection = Range("R" & row1) & "%"
Selection.Interior.ColorIndex = 36

Range(Cells(row1, col1 + Range("p" & row1) + Range("Q" & row1) + _
Range("R" & row1) + 1), Cells(row1, col1 + Range("P" & row1) + _
Range("Q" & row1) + Range("R" & row1) + Range("S" & row1) -
1)).Select

Selection.Merge
Selection = Range("S" & row1) & "%"
Selection.Interior.ColorIndex = 34

I don't understand what you're doing after column P, but:

dim HowManyCols as long
with worksheets("Somesheetnamehere")
howmanycols = .cells(row1,"P").value
if howmanycols 0 then
.cells(row1,"AA").resize(1,howmanycols).merge
end if
...

jlclyde wrote:

Sub Macro6()
Dim row1 As Long
Dim col1 As Long

Set MYrange = Application.InputBox(Prompt:="Please select
range", _
Title:="Tihs Siht Kcuf", Type:=8)
row1 = MYrange.Row
col1 = MYrange.Column

Range(MYrange, Cells(row1, col1 - 1 + Range("P" &
row1))).Select
Selection.Merge
Selection = Range("P" & row1) & "%"
Selection.Interior.ColorIndex = 35

I have been working on this all night and this is the best I can come
up with. I need to merge cells based on what is in P, Q, R, S, and T

So for instance IF Range("P" row1) <0 then
Starting at Range("AA" row1) I want the cells to
merge for the number that is in "P". So if the number is 10 then it
woudl merge cells AA:AJ. Then merge the cells to the right based on
what is in Q. The tricky part is that any of these at anytime can be
0 and I can not think of how to do this.

Thanks,
Jay

Here is all of my code.

Set myrange2 = Cells(row1, col1 + Range("P" & row1))
Range(myrange2, Cells(row1, col1 - 1 + Range("Q" &
row1) + Range("P" & row1))).Select
Selection.Merge
Selection = Range("Q" & row1) & "%"

Range(Cells(row1, col1 + Range("p" & row1) + Range("Q" & row1)),
Cells(row1, _
col1 + Range("P" & row1) + Range("Q" & row1) + Range("R" &
row1))).Select
Selection.Merge
Selection = Range("R" & row1) & "%"
Selection.Interior.ColorIndex = 36

Range(Cells(row1, col1 + Range("p" & row1) + Range("Q" & row1) + _
Range("R" & row1) + 1), Cells(row1, col1 + Range("P" & row1) + _
Range("Q" & row1) + Range("R" & row1) + Range("S" & row1) -
1)).Select

Selection.Merge
Selection = Range("S" & row1) & "%"
Selection.Interior.ColorIndex = 34

--

Dave Peterson

This will do one row (row 2 for this sample - you need to loop on row1, but wasn't sure where you
set that value), and I wasn't sure what you wanted in the cells after merging, so I just put a
label....

Sub TryNow()
Dim Row1 As Long
Dim myC As Range
Dim myR As Range
Dim myM As Range
Dim I As Integer
Dim W As Integer

Row1 = 2
'Start your loop here instead of setting Row1 with
'For Row1 = 2 to Whatever.....

Set myM = Range("Z" & Row1)

W = 1

Set myR = Range("P" & Row1).Resize(1, 5)
For Each myC In myR
I = myC.Value
If I 0 Then
With myM.Offset(0, W).Resize(1, myC.Value)
.Merge
.Value = "Merged"
End With
W = W + I
End If
Next myC

'End your loop here with Next Row1

End Sub

--
HTH,
Bernie
MS Excel MVP

"jlclyde" wrote in message
...
Sub Macro6()
Dim row1 As Long
Dim col1 As Long

Set MYrange = Application.InputBox(Prompt:="Please select
range", _
Title:="Tihs Siht Kcuf", Type:=8)
row1 = MYrange.Row
col1 = MYrange.Column

Range(MYrange, Cells(row1, col1 - 1 + Range("P" &
row1))).Select
Selection.Merge
Selection = Range("P" & row1) & "%"
Selection.Interior.ColorIndex = 35

I have been working on this all night and this is the best I can come
up with. I need to merge cells based on what is in P, Q, R, S, and T

So for instance IF Range("P" row1) <0 then
Starting at Range("AA" row1) I want the cells to
merge for the number that is in "P". So if the number is 10 then it
woudl merge cells AA:AJ. Then merge the cells to the right based on
what is in Q. The tricky part is that any of these at anytime can be
0 and I can not think of how to do this.

Thanks,
Jay

Here is all of my code.

Set myrange2 = Cells(row1, col1 + Range("P" & row1))
Range(myrange2, Cells(row1, col1 - 1 + Range("Q" &
row1) + Range("P" & row1))).Select
Selection.Merge
Selection = Range("Q" & row1) & "%"

Range(Cells(row1, col1 + Range("p" & row1) + Range("Q" & row1)),
Cells(row1, _
col1 + Range("P" & row1) + Range("Q" & row1) + Range("R" &
row1))).Select
Selection.Merge
Selection = Range("R" & row1) & "%"
Selection.Interior.ColorIndex = 36

Range(Cells(row1, col1 + Range("p" & row1) + Range("Q" & row1) + _
Range("R" & row1) + 1), Cells(row1, col1 + Range("P" & row1) + _
Range("Q" & row1) + Range("R" & row1) + Range("S" & row1) -
1)).Select

Selection.Merge
Selection = Range("S" & row1) & "%"
Selection.Interior.ColorIndex = 34

Dave,
i am trying to show how many of each of these things happened
graphically but not as a graph. After "P" I need it to go to "Q" and
do the same thing over. If "P" was 10 then AA:Aj woudl be merged. If
"Q" was 10 then AK:AT woudl be merged. If "R" = 10 then AU:AD woudl
be merged. Plus if "P" was 0 then I woudl want to start merging AA:on
with what is in "Q". Does that make more sense?
I need this to show an employee opion survey. Currently it is as a
graph, but you can not hide rows and hide the graphics from the
different groups. Or atleast I am not aware of a way. Instead of
making a new spread sheet for each of the 19 departments, I was hoping
this woudl be easier.

Thanks,
Jay

On Jan 11, 9:28*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
This will do one row (row 2 for this sample - you need to loop on row1, but wasn't sure where you
set that value), and I wasn't sure what you wanted in the cells after merging, so I just put a
label....

Sub TryNow()
Dim Row1 As Long
Dim myC As Range
Dim myR As Range
Dim myM As Range
Dim I As Integer
Dim W As Integer

Row1 = 2
'Start your loop here instead of setting Row1 with
'For Row1 = 2 to Whatever.....

Set myM = Range("Z" & Row1)

W = 1

Set myR = Range("P" & Row1).Resize(1, 5)
For Each myC In myR
* *I = myC.Value
* *If I 0 Then
* * * With myM.Offset(0, W).Resize(1, myC.Value)
* * * * *.Merge
* * * * *.Value = "Merged"
* * * End With
* * * W = W + I
* *End If
Next myC

'End your loop here with * *Next Row1

End Sub

--
HTH,
Bernie
MS Excel MVP

"jlclyde" wrote in message

...

Sub Macro6()
Dim row1 As Long
Dim col1 As Long

* * * * *Set MYrange = Application.InputBox(Prompt:="Please select
range", _
* *Title:="Tihs Siht Kcuf", Type:=8)
* *row1 = MYrange.Row
* *col1 = MYrange.Column

* * * * * *Range(MYrange, Cells(row1, col1 - 1 + Range("P" &
row1))).Select
* * * * * *Selection.Merge
* * * * * *Selection = Range("P" & row1) & "%"
* * * * * *Selection.Interior.ColorIndex = 35

I have been working on this all night and this is the best I can come
up with. *I need to merge cells based on what is in P, Q, R, S, and T

So for instance IF Range("P" row1) *<0 then
* * * * * * * * * * *Starting at Range("AA" row1) I want the cells to
merge for the number that is in "P". *So if the number is 10 then it
woudl merge cells AA:AJ. *Then merge the cells to the right based on
what is in Q. *The tricky part is that any of these at anytime can be
0 and I can not think of how to do this.

Thanks,
Jay

Here is all of my code.

* * * * * * * *Set myrange2 = Cells(row1, col1 + Range("P" & row1))
* * * * * * * *Range(myrange2, Cells(row1, col1 - 1 + Range("Q" &
row1) + Range("P" & row1))).Select
* * * * * * * *Selection.Merge
* * * * * * * *Selection = Range("Q" & row1) & "%"

* *Range(Cells(row1, col1 + Range("p" & row1) + Range("Q" & row1)),
Cells(row1, _
* *col1 + Range("P" & row1) + Range("Q" & row1) + Range("R" &
row1))).Select
* *Selection.Merge
* *Selection = Range("R" & row1) & "%"
* *Selection.Interior.ColorIndex = 36

* *Range(Cells(row1, col1 + Range("p" & row1) + Range("Q" & row1) + _
* *Range("R" & row1) + 1), Cells(row1, col1 + Range("P" & row1) + _
* *Range("Q" & row1) + Range("R" & row1) + Range("S" & row1) -
1)).Select

* *Selection.Merge
* *Selection = Range("S" & row1) & "%"
* *Selection.Interior.ColorIndex = 34- Hide quoted text -

- Show quoted text -

This does exactly what I want. I will add the titles and fill colrs.
thank you very much for this.
Jay