Adding lines that affect formula

[wilg]

Hi, have below code that is designed to compile a list of names into one cell. The issue I have is that if I insert new lines in the middle of the rows the formula below will not automatically incorperate the new inserted lines. Is there a way to mod this so for future if I want to insert lines it will automatically account for them?

=TRIM(AI487&" "&AI485&" "&AI483&" "&AI481&" "&AI479&" "&AI477&" "&AI475&" "&AI473&" "&AI471&" "&AI469)

eg. this will give me (Mike,Joe,Mary,Ed, etc...) I need it to auto correct for the new lines if I insert them.

[Aussiebear]

Use the $ sign to lock your cell references

[p45cal]

Not an easy way that I can think of, however a udf used thus in a sheet:
=listnames(AI469:AI487)

backed up by this macro in a standard module:
[vba]Function ListNames(xxx As Range)
lastcell = xxx.Cells.Count
If Int(lastcell / 2) = lastcell / 2 Then lastcell = lastcell - 1
For i = lastcell To 1 Step -2
ListNames = ListNames & xxx(i).Value & ", "
Next i
ListNames = Left(ListNames, Len(ListNames) - 2)
End Function
[/vba] The reference in the formula will adjust as with all formulae if you add cells within that referred-to range.
It always includes the top of the range, but returns only alternate cells, and in reverse order as your formula does.
It's designed for single column/single row use rather than for a multi-column and multi-row range.
Attachment 4898

[macropod]

I'd suggest this variation:
[vba]Function ListNames(RngCells As Range, Offset As Boolean)
Dim oCel As Range
For Each oCel In RngCells
If oCel.Row Mod 2 = Offset ^ 2 Then _
If oCel.Value <> "" Then _
ListNames = ListNames & oCel.Value & ", "
Next
ListNames = Left(ListNames, Len(ListNames) - 2)
End Function[/vba]
combined with the worksheet formula:
=Listnames(AI469:AI487,1)

To reverse the display order, change:
ListNames & oCel.Value & ", "
to:
oCel.Value & ", " & ListNames

The advantage the above functions have is that empty cells in the target range are skipped and changing the '1' in the formula to '0' allows processing of even-numbered rows instead.

[p45cal]

To reverse the display order, change:
ListNames & oCel.Value & ", "
to:
oCel.Value & ", " & ListNames

I've been incredibly dim-witted, going to all the bother of finding the last cell and moving up the list, when the above would have done. Here it is again, streamlined a bit:[vba]Function ListNames(xxx As Range)
For i = 1 To xxx.Cells.Count Step 2
ListNames = xxx(i).Value & ", " & ListNames
'If Not IsEmpty(xxx(i)) Then ListNames = xxx(i).Value & ", " & ListNames 'enable this line and disable the line above to skip empty cells.
Next i
ListNames = left(ListNames, Len(ListNames) - 2)
End Function[/vba] macropod, I see something in your function that might raise the user's eyebrow a bit; should the user choose to delete or add a row (an odd number of cells/rows) above the target range, he'll get a different result, perhaps without noticing it. If there are lots of such formulae then he'll have some editing to do.

I have incorporated your idea of skipping empty cells as a commented-out line to replace the line above it.

[wilg]

Wow, I feel ive been getting much better at VBA but you guys just are amazing. Quick question (Silly prob), in your code do i replace "xxx" with a cell number, that would be the last cell for ref or is that a variable?

[p45cal]

Wow, I feel ive been getting much better at VBA but you guys just are amazing. Quick question (Silly prob), in your code do i replace "xxx" with a cell number, that would be the last cell for ref or is that a variable?

The UDF has been written for use on the worksheet in mind.
You just paste the code, as is, no replacing anything with anything, into a standard module of the workbook concerned. Then go to the worksheet and start entering your formulae such as:
=listnames(AI469:AI487)
You need do nothing else, apart from save the workbook at least once to save the function with it.

Should I be reading into your question that you want to use the function within VBA?

[macropod]

Hi p45cal,

With my function, a change in the rows could be accommodated by calling the UDF with a formula like:
=ListNames(AI469:AI487,MOD(ROW(AI469),2)=1)
Moving the list up or down any number of rows would have no effect when calling the UDF this way, since the start row determines the 'MOD=' TRUE/FALSE result.

[wilg]

Hi guys, thats it. I works great. Thanks as always. I can't believe the expertise you guys have.

[wilg]

Hi guys, sorry for a later respons but I just found out that the code I am using from you is stopping another code from working for some reason....
I have your code..

[VBA]Function ListNamesAdmin(RngCells As Range, Offset As Boolean)
Dim oCel As Range
For Each oCel In RngCells
If oCel.Row Mod 2 = Offset ^ 2 Then _
If oCel.Value <> "" Then _
ListNamesAdmin = ListNamesAdmin & oCel.Value & ", "
Next
ListNamesAdmin = Left(ListNamesAdmin, Len(ListNamesAdmin) - 2)
End Function[/VBA]

and this code is preventing this code from working on the same sheet...

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo error
If (Target.Column = 1) Then

If Target.Row + 2 < Range("a" & Range("ak301")).Row Then

If (Target.Value) = "" And Target.Offset(-2, 1).Value = "" Then

Call MacroUprotectAll
Rows((Target.Row) & ":" & (Target.Row + 1)).EntireRow.Hidden = True
Call ProtectAll
Target.Select
Else
Call MacroUprotectAll

Rows((Target.Row + 2) & ":" & (Target.Row + 3)).EntireRow.Hidden = False
Call ProtectAll
Target.Select
End If
End If
End If
error:
End Sub[/VBA]


It's like the function is killing the worksheet_change event from happening fully....any thoughts?

[p45cal]

I see no reason, at first glance, why one should prevent the other from working.
Remove the On Error Goto error temporarily to see where the code bugs out.

[wilg]

I removed the on error and the sheet calculates but the hide lines does not happen. I know if I delete the formula for listnames the code for hiding lines works.

[wilg]

Hmmm, the code I have that hides lines the target cell is a drop down menu for names. It worked prior to me adding the listnames function. I just discovered that if I press space bar then back space giving me the "" like in the hiderows code it works. as well as if I type the name of a person it will then unhide the row.
So what it seems is that when I select a name from the drop down menu it has stopped working due to the function as it works without the function code.
Does this give you any idea?

[wilg]

Ok, so I have a bug that shows at

If (Target.Column = 1) Then

I tried putting the line hide code before worksheet calculate and this is what I got as a bug.
Is it that before it runs line hide, the target.column goes to where I have the listnames which is in column AI and my drop down menu that I am using is in column A?

[p45cal]

This is nigh on impossible to diagnose remotely.
I can only suggest you put breakpoints (F9) on all first lines of subs and functions involved (including MacroUprotectAll and ProtectAll) and make your change on the sheet and then step through the code with F8. Disable all On Error/Goto/Resume while you do it, to see where it's going wrong.

[wilg]

After days of still trying to work this out I have a question, if anyone can help me. I use a drop down menu and select from the drop down menu a name "" (blank), the function listnames in above threads somehow affect it by not allowing my worksheet_change code from continuing.
If I key delete in the drop down menu it then registers "" and my lines hide. but not by selecting "" from the drop down menu.
If I delete Listnames function it works fine too.

I have lost alot of time on this one, any direction is very much appreciated....

[Bob Phillips]

Try posting the workbook.

[wilg]

is there any way to change this to a sub? and keeping formula

=listnames(AI469:AI487)

in the specified cell return the same result?



[VBA]

Function ListNamesAdmin(RngCells As Range, Offset As Boolean)
Dim oCel As Range
For Each oCel In RngCells
If oCel.Row Mod 2 = Offset ^ 2 Then _
If oCel.Value <> "" Then _
ListNamesAdmin = ListNamesAdmin & oCel.Value & ", "
Next ListNamesAdmin = Left(ListNamesAdmin, Len(ListNamesAdmin) - 2) End Function

[/VBA]

after much work it come down that the function formula is stopping the hide row from happening. So I would like to call the function as a sub at the end of the hide row code.

[Bob Phillips]

No.