Messagebox listing going down instead of across

[tiredofit]

The code below lists the address of all cells in a pre-defined range whose value is zero.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim Rng As Range
    
    With ws
    
        Set Rng = .Range(.Cells(1, 1), .Cells(1,1000))
    
    End With
    
    Dim ZeroColl As Collection
    Set ZeroColl = New Collection
    
    Dim RngElement As Range
    
    For Each RngElement In Rng
    
        If RngElement.Value = 0 Then ZeroColl.Add RngElement.Address
                    
    Next RngElement
    
    Dim ZeroCollCount As Long
    
    ZeroCollCount = ZeroColl.Count
    
    Dim i As Long
    
    Dim j As String
    
    For i = 1 To ZeroCollCount
    
        j = j & "," & ZeroColl(i)
    
    Next i
    
    Set Rng = Nothing
    
    Set RngElement = Nothing
    
    MsgBox " These cells are empty:" & vbNewLine & vbNewLine & Right(j, Len(j) - 1)

What I want is to have the message box display the addresses in a list going down, instead of in a single continuous sentence, ie I want:

A20
A25
A33

instead of:

A20, A25, A33

How can it be done?

Thanks


[/FONT]

 

[Fluff]

How about

[FONT=Verdana] j = j & vblf & ZeroColl(i)[/FONT]

 

[Joe4]

Try changing this line:

[FONT=Verdana]j = j & "," & ZeroColl(i)[/FONT]

to this:

[FONT=Verdana]j = j & vbCrLf & ZeroColl(i)[/FONT]

 

[tiredofit]

Thanks to both, exactly what I wanted.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[tiredofit]

Messagebox listing going down instead of across (part 2)

Further to my post here:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.mrexcel.com/forum/excel-questions/1113228-messagebox-listing-going-down-instead-across.html

I achieved what I wanted with the help from Fluff and Joe4.

However what I find now is the list is very long, to the extent I cannot see the OK button!

Is there way to limit the number of rows and then have the results within the same message box but in column 2?
[/FONT]
At present it looks like this:

A25
A30
A33
A40
A49

but imagine it going on in a single long column.

Instead I would like it as follows:

A25    A40
A30    A49
A33

Is that possible?

Thanks

 

[Joe4]

Re: Messagebox listing going down instead of across (part 2)

I have merged your most recent post back to the original thread.
Directly related follow-ups that are dependent upon the previous question should continue on in the same thread. You should only post to a new thread if it is a new question that is not dependent upon the previous question.

 

[tiredofit]

Re: Messagebox listing going down instead of across (part 2)

Thanks.

I thought posting it separately avoided confusion.

 

[Joe4]

Re: Messagebox listing going down instead of across (part 2)

I thought posting it separately avoided confusion.

What confusion?

You have two people helping you. Why would you not want them to continue to help you?
Since they are already invested in your problem, they are some of the most likely people to help you see it through.
By posting to a new thread, they may not see it, so you may not get help from them.

Back to your question, this could get pretty tricky. Let's take a step back.
What is the ultimate goal here? For what purpose do you want to return a a large list of cell addresses to a message box?
What do you mean to do with it? Since it is a message box, it really cannot be used for anything, other than to look at and read.

 

[tiredofit]

Re: Messagebox listing going down instead of across (part 2)

I just want the message box to return a list of cell addresses whose values are zero.

Then the user can clean up the data however they wish.

If the message box is not a good idea, I suppose I can paste the results onto a worksheet instead.