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]

 

[Joe4]

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

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

I am thinking that might be a better option.
In order to clean up the data, they would need to close out of the Message Box, at which point, the list they need to use to identify which cells to clean up will be gone!

 

[Fluff]

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

I'd agree with Joe, putting it on a worksheet would be better.
How about

    Dim Rng As Range, RngElement As Range
    Dim i As Long
    With Ws
    
        Set Rng = .Range(.Cells(1, 1), .Cells(1, 1000))
    
    End With
    For Each RngElement In Rng
        If RngElement.Value = 0 Then
            i = i + 1
            Cells(i, 1).Value = RngElement.Address
        End If
    Next RngElement

 

[tiredofit]

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

Thanks, that'll do nicely.

 

[Fluff]

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

Glad we could help & thanks for the feedback