Merge Macro

[jlclyde]

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]

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

 

[Bernie Deitrick]

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

 

[jlclyde]

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 AAn
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

 

[jlclyde]

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














- Show quoted text -

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