MsgBox - Dynamic Range

[Nordicrx8]

Hey all!

I'm using a MSG Box to display values from a range of cells. The below code is working, but the range actually needs to be dynamic.

There is a formula in H2-H400 doing a lookup. A value (That I want to display in the MsgBox) will only appear in this range if the user updates the workbook. If no changes are made, the values in column H will have a zero in it.

Ideally, I'd like it to identify what cells in the range have a zero in them, and omit them from the values printed in the MsgBox.

As another option, I wrote a formula in i1 & J1 (on sheet "Verify") that automatically update to correct range - we could do an indirect to those cells.

Been struggling on getting either of these options working and wanted to reach out for a helping hand.

Thanks all!

Sub VER_Bulk()

Set Rng = Sheets("Verify").Range("H1:H7")

For Row = 2 To Rng.Rows.Count
For col = 1 To Rng.Columns.Count

Values = Values & Rng.Cells(Row, col).Value _
& vbTab & vbTab
Next col
Values = Values & vbNewLine
Next Row

MsgBox "Updates have been made to the following zones: " _
& vbLf & vbLf & Values
End Sub

 

[Georgiboy]

Perhaps the below will help:

Sub VER_Bulk()
    Dim rng As Range, sValues As Variant, rngAddr As String

    Set rng = Sheets("Verify").Range("H1:H7")
    rngAddr = rng.Address(, , , True)
    sValues = Evaluate("FILTER(" & rngAddr & "," & rngAddr & "<>0)")
    
    MsgBox "Updates have been made to the following zones: " _
        & vbNewLine & vbNewLine & Join(Application.Transpose(sValues), vbNewLine)
End Sub

 

[Nordicrx8]

Perhaps the below will help:

Sub VER_Bulk()
    Dim rng As Range, sValues As Variant, rngAddr As String

    Set rng = Sheets("Verify").Range("H1:H7")
    rngAddr = rng.Address(, , , True)
    sValues = Evaluate("FILTER(" & rngAddr & "," & rngAddr & "<>0)")
   
    MsgBox "Updates have been made to the following zones: " _
        & vbNewLine & vbNewLine & Join(Application.Transpose(sValues), vbNewLine)
End Sub

Works Perfectly! Thanks so much! Now I am thinking about displaying two columns of data, using the same criteria. (H1:i10) I tried making the change, but it broke it. anything special I need to add in for it to display two columns? thanks again for your help!!

 

[Georgiboy]

Are you trying to have the value in column I be displayed next to the value in column H in the message box?
Can column I have a value other than 0 as well as column H?
If column H has a 0 should it omit column I for that row?

 

[Nordicrx8]

Are you trying to have the value in column I be displayed next to the value in column H in the message box?
Can column I have a value other than 0 as well as column H?
If column H has a 0 should it omit column I for that row?

Yes, have them side by side. I was going to have them be a separate message box, but I think It makes more sense to just have the two columns with a header. (same conditions, if zero, do not show) They they both will hold different values, but always be text.

 

[Georgiboy]

Maybe the below:

Sub VER_Bulk()
    Dim sValues As Variant
    Dim rng As Range, cRng As Range
    Dim rngAddr As String, cRngAddr As String

    Set rng = Sheets("Verify").Range("H1:I7")
    Set cRng = Sheets("Verify").Range("H1:H7")
    
    rngAddr = rng.Address(, , , True)
    cRngAddr = cRng.Address(, , , True)

    sValues = Evaluate("BYROW(FILTER(" & rngAddr & "," & cRngAddr & "<>0),LAMBDA(x,TEXTJOIN("" "",,x)))")
    
    MsgBox "Updates have been made to the following zones: " _
        & vbNewLine & vbNewLine & Join(Application.Transpose(sValues), vbNewLine)
End Sub

 

[Nordicrx8]

Yes! That worked! Totally not following the code as it's way over my head (will have to google some of it later to figure out what some of this code does) but my hat is off to you on writing this without seeing my workbook, and for doing it at 2AM!

Any way we could clean up the display of the content to make it look more like the source data? (alignment)

Thanks again for all your help!