Setting string based on conditions

[brownti via OfficeKB.com]

Does anyone have some example code that will set values in a string based on
a condition? For example if row 1 is hidden go on to row 2. If Row 2 is not
hidden set MSGBODY = Range("A2"). If Row 3 is not hidden set MSGBODY = (All
previous conditions and also) Range("A3"). And so on....

I cant figure out how to keep one condition will adding another condition to
the string. What i have is seven rows, some are hidden and some are not.
Depending on if the row is hidden or not i want to put a value from that row
into a string and then use that string in an email message using .Body. Any
ideas?

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myMsg As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Worksheets("sheet9999").Range("a1:A7")

myMsg = ""
For Each myCell In myRng.Cells
If myCell.EntireRow.Hidden = True Then
'do nothing
Else
'I put a space between each value
myMsg = myMsg & " " & myCell.Value
End If
Next myCell

If myMsg = "" Then
'nothing found
Else
'get rid of the leading space
myMsg = Mid(myMsg, 2)
End If

MsgBox myMsg

End Sub

 

[Guest]

Try this:

Sub test()
For i = 1 To 7
Cells(i, 1).Select
If Selection.EntireRow.Hidden = True Then
'do nothing
Else
Set msgbody = Cells(i, 1)
MsgBox msgbody
Exit Sub
End If

Next
End Sub

It should give you some ideas

Peter Atherton

 

[Guest]

God I don't read very well at times - Good one Dave


Peter