0

I am trying to modify CuberChase's code on putting delimited string tables into a word document. As far as I know, his code has the rFromRange value being static (IE, hiding rows or columns is not accounted for).

I have tried changing the range to only be .SpecialCells(xlCellTypeVisible), but this ends up cutting the table short, rather than skipping the hidden rows/columns filtered out to still create the whole rest of the table. I have tried at length to figure out a solution by inserting vbCr and vbTab breaks on error, but I'm really not sure where to turn. Does anyone know how to set this array up to be able to skip columns or rows that end up being hidden on the original worksheet? Thank you for your time, please ask any additional questions for material that I have not covered here. 

Function BuildDataString(rFromRange As Range) As String
Dim sData As String, nrRow As Long, nrCol As Integer, iTotalColumns As Integer

'Convert the input range to a variable and determine the number of columns
vData = rFromRange.Value
iTotalColumns = UBound(vData, 2)


'Loop through all the elements in the array
For nrRow = LBound(vData, 1) To UBound(vData, 1)
    For nrCol = 1 To iTotalColumns
        'Depending on what type of data is encountered either add it to the string or substitute something
        'You'll want to modify this as needed
        If IsError(vData(nrRow, nrCol)) Then
            sData = sData & "Error"
        ElseIf vData(nrRow, nrCol) = "" Or vData(nrRow, nrCol) = 0 Or vData(nrRow, nrCol) = "-" Then
            sData = sData & VBA.Chr$(150)
        Else
            sData = sData & vData(nrRow, nrCol - iIncrement)
        End If

        'Use tab delimiters for Word to know where the columns are
        If nrCol < iTotalColumns Then sData = sData & vbTab
    Next nrCol

    'Add a carriage return for each new line
    If nrRow < UBound(vData, 1) Then sData = sData & vbCr
Next nrRow

'Return the completed string
BuildDataString = sData
End Function
Sebastian Soldner
  • 1
  • 1
  • Can't you check `rFromRange.Rows(nrRow).Hidden` in the `For` for the columns and `Exit For` if true? – Cindy Meister Jul 26 '18 at 14:22
  • Doesn't this just kill the loop once it encounters a hidden row? If a row is hidden in the middle of the table, I would like the script to "skip" that hidden row and continue onto the next rows until done. For instance. If one of the ranges is 10 rows long and 10 columns wide, and row 6 is hidden and column 8 is hidden, I want the array seek to skip row 6 and column 8 and continue grabbing rows 7-10 and columns 9-10 and add them to the table built in word. – Sebastian Soldner Jul 26 '18 at 19:21
  • You don't show us *all* the code - the code that gets `rFromRange`. But my assumption is that this is a block of cells? So if you test a row index within that block for its Hidden property it will tell you that. Did you try it? I did check before I proposed the approach... – Cindy Meister Jul 26 '18 at 19:29
  • The rest of the code is in the original post I linked. I used your code at first and it did skip a row, but it still left it blank (rather than delete it). What I had to do was make the code like this, where I also have a column exit and do not add a line break if a row is false.The updated decider is: If nrrow < UBound(vdata, 1) And rFromRange.Rows(nrrow).Hidden = False Then sData = sData & vbCr. – Sebastian Soldner Jul 31 '18 at 11:17

0 Answers0