Converts sheet range to ANString, can work on any continued range, 1 or 2 dimensions.
Can pass sheet name, workbook, etc. as usual.
Function Range2ANStr(RangeAddress, Optional WB = "This", Optional Shee = "Active", Optional Sepa = "|")
' Converts range of 1 column (or 1 row) into ANStr separated by Sepa
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(WB).Worksheets(1).Name
'If ColumnNam = "" Then ColumnNam = "A"
Rett = ""
For Each Cee In Workbooks(WB).Worksheets(Shee).Range(RangeAddress)
Item1 = Cee.Value
If IsError(Item1) Then Item1 = CStr(Item1)
If Item1 = "" Then GoTo NextI1
If Rett > "" Then Rett = Rett & Sepa
Rett = Rett & Item1
NextI1:
DoEvents
Next
Range2ANStr = Rett
End Function
' Converts range of 1 column (or 1 row) into ANStr separated by Sepa
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(WB).Worksheets(1).Name
'If ColumnNam = "" Then ColumnNam = "A"
Rett = ""
For Each Cee In Workbooks(WB).Worksheets(Shee).Range(RangeAddress)
Item1 = Cee.Value
If IsError(Item1) Then Item1 = CStr(Item1)
If Item1 = "" Then GoTo NextI1
If Rett > "" Then Rett = Rett & Sepa
Rett = Rett & Item1
NextI1:
DoEvents
Next
Range2ANStr = Rett
End Function
RangeAddress, Optional WB = "This", Optional Shee = "Active", Optional Sepa = "|"
Views 97
Downloads 49
CodeID
DB ID