macro to create lists

  • Thread starter Thread starter bfuzzeq
  • Start date Start date
B

bfuzzeq

Hi,

I have a spreadsheet where each row contains anywhere from 1 to 8 names
($G$4:$N$4). I need to combine these names into a list box for use in a
pivot chart. I know how to create the list box using data validation,
and I recorded a macro while doing this, but the macro is too specific.
It just repeats the first data selection range. I need the macro to be
variable depending on the row ($G$5:$N$5, $G$6:$N$6, $G$7:$N$7, ...).
Obviously, I have no VB Coding experience and my Excel skills are very
basic. I spent a couple hours sifting through messages and either
haven't found what I'm looking for, or didn't understand what I was
looking at. Any help would be greatly apreciated.

Sincerely,

Bfuzzeq

If it helps here is the macro I generated:


Code:
--------------------
Sub Macro7()
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$G$4:$N$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveWindow.SmallScroll ToRight:=4
End Sub
 
I discovered that posting the drop down list in the pivot chart was no
as simple as I thought so I endned up using a solution I saw in anothe
Post, create another column with the formula =H2&", "&B2 ... This work
well enough and the names transfer over to the pivot chart which i
what matters., but I have a different problem in that the pivot char
keeps creating 'total' rows for each column at each line entry. It's
little difficult to explain so I'm attaching the file (pdf). How can
get rid of these extra lines and the "Totals"

+-------------------------------------------------------------------
|Filename: cme_062905b.pdf
|Download: http://www.excelforum.com/attachment.php?postid=3549
+-------------------------------------------------------------------
 
Back
Top