I am trying to extract all the visible worksheets in a few workbooks into double pipe delimited text files. For this I want to take into consideration only the cells that are 'not hidden' on a worksheet.
As per my understanding a cell can be hidden when:
- either its row / column has been manually hidden by user
- or its row has been filtered out due to autofilter applied on header or either of the columns
I want to exclude the manually hidden rows / columns but want to take into consideration the rows that have got hidden due to the applied autofilter.
Can someone suggest me on how to identify cells of this kind. I am currently using the following code, but this leaves out the cells hidden by autofilter as well.
For RowNdx = StartRow To EndRow
If Rows(RowNdx).Hidden = False Then
WholeLine = ""
For ColNdx = StartCol To EndCol
If Columns(ColNdx).Hidden = False Then
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Value
End If
WholeLine = WholeLine & CellValue & Sep
End If
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
End If
Next RowNdx
Any prompt help on this shall be highly appreciated!!
As per my understanding a cell can be hidden when:
- either its row / column has been manually hidden by user
- or its row has been filtered out due to autofilter applied on header or either of the columns
I want to exclude the manually hidden rows / columns but want to take into consideration the rows that have got hidden due to the applied autofilter.
Can someone suggest me on how to identify cells of this kind. I am currently using the following code, but this leaves out the cells hidden by autofilter as well.
For RowNdx = StartRow To EndRow
If Rows(RowNdx).Hidden = False Then
WholeLine = ""
For ColNdx = StartCol To EndCol
If Columns(ColNdx).Hidden = False Then
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Value
End If
WholeLine = WholeLine & CellValue & Sep
End If
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
End If
Next RowNdx
Any prompt help on this shall be highly appreciated!!