Hi all,
I appreciate everyone looking at this post seeing if they can help! This has been eluding me for some time because VBA is not my strong suit. It took some deep-diving to get what I have and adding in this condition has been unsuccessful.
I have a worksheet that I want to concatenate into another sheet - it is 6 columns starting from a fixed cell and copies any data over with a delimiter - code below. The sheet that it is pulling from has conditional formatting that hide 0s along with formulas and I would prefer if it did not include them and concatenate as if there was a blank instead. Is there an easier way to achieve what I am looking for?
Thank you in advance!!
I appreciate everyone looking at this post seeing if they can help! This has been eluding me for some time because VBA is not my strong suit. It took some deep-diving to get what I have and adding in this condition has been unsuccessful.
I have a worksheet that I want to concatenate into another sheet - it is 6 columns starting from a fixed cell and copies any data over with a delimiter - code below. The sheet that it is pulling from has conditional formatting that hide 0s along with formulas and I would prefer if it did not include them and concatenate as if there was a blank instead. Is there an easier way to achieve what I am looking for?
Thank you in advance!!
VBA Code:
Dim Rng As Range
Dim EntryWs As Worksheet
Dim EntryLastRow As Long, x As Long
Set EntryWs = ThisWorkbook.Worksheets("Entry")
EntryLastRow = EntryWs.Range("A" & Rows.Count).End(xlUp).Row
Set Rng = EntryWs.Range("F5:K" & EntryLastRow)
Dim Column_Numbers() As Variant
Column_Numbers = Array(1, 2, 3, 4, 5, 6)
Separator = "|"
Output_Worksheet = "Upload"
Output_Cell = "E11"
For i = 1 To Rng.Rows.Count
Output = ""
For j = LBound(Column_Numbers) To UBound(Column_Numbers)
If j <> UBound(Column_Numbers) Then
Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
Else
Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
End If
Next j
Worksheets(Output_Worksheet).Range(Output_Cell).Cells(i, 1) = Output
Next i
End Sub