Strip out certain rows that have merged cells

[B Roberson]

I wish I could attach a sample but I am not sure how to attach a file in this group. So here goes.

I received an email file with the following setup.

Columns A&B are each individually merged cells. The rows on columns A&B depend on the group of data and how many records are contained in it includinggroup summaries and item summaries etc. I do not need anything in columns A or B for what I am wanting to do. However, if you go to highlight an area, because of the merged cells you get all this together in the highlighted area.

Columns C&D have merged cells for the individual items but not the summaries of the items or the group summaries.

For the item summary and the group summaries, the columns merged together are columns C though H. Let's say there are three records making up this item summary. The problem I would like to solve without doing this for 500 record items is to first undo the cell merge formatting in all the above descibed situations. Then, for Columns C and D once the merged cells are eliminated, I want to populate the record below that would otherwise be blank at this point with the information that is in column C and column D. If there are two records within an item summary, then I need to copy the column C record down to the next row, but if there are three or more then I need to copy the column C information down how ever many it takes to fill out the information for column C. The same thing needs to happen for column D as described for column c. There would always be an identical copy for column C and column D information for each item described.

Once all the merged cells are eliminated and the column C and D are properly populated, then I can go and do a filter to filter out the individual records I do not want in my new report.

If there is a way to attach files in the google groups so I can better illustrate, then please let me know how this works.

Thanks

 

[Claus Busch]

Hi,

Am Wed, 1 May 2013 06:58:10 -0700 (PDT) schrieb B Roberson:

If there is a way to attach files in the google groups so I can better illustrate, then please let me know how this works.

you can upload your file to a file hoster and post us here the link:

Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Box.Net: http://www.box.net/files


Regards
Claus Busch

 

[RB Smissaert]

Here some functions that you may find useful to help with this:


Function SheetHasMergedCells(Optional shSheet As Worksheet) As Byte

'checks the sheet for merged cells and will return:
'0 - no merged cells
'1 - at least two merged cells
'3 - all the cells merged into one!
'--------------------------------------------------
Dim vHasMerged As Variant 'True, False, or Null

If shSheet Is Nothing Then
Set shSheet = ActiveSheet
End If

vHasMerged = shSheet.Cells.MergeCells

If vHasMerged = True Then
SheetHasMergedCells = 3
Else
If vHasMerged = False Then
SheetHasMergedCells = 0
Else 'IsNull(vHasMerged) will be True
SheetHasMergedCells = 1
End If
End If

End Function

Function RowHasMergedCells(lRow As Long, Optional shSheet As Worksheet) As
Boolean

Dim vHasMerged As Variant 'True, False, or Null

If shSheet Is Nothing Then
Set shSheet = ActiveSheet
End If

vHasMerged = shSheet.Rows(lRow).Cells.MergeCells

If vHasMerged = True Then
RowHasMergedCells = True
Else
If vHasMerged = False Then
RowHasMergedCells = False
Else 'IsNull(vHasMerged) will be True
RowHasMergedCells = True
End If
End If

End Function

Function ColumnHasMergedCells(lColumn As Long, Optional shSheet As
Worksheet) As Boolean

Dim vHasMerged As Variant 'True, False, or Null

If shSheet Is Nothing Then
Set shSheet = ActiveSheet
End If

vHasMerged = shSheet.Columns(lColumn).Cells.MergeCells

If vHasMerged = True Then
ColumnHasMergedCells = True
Else
If vHasMerged = False Then
ColumnHasMergedCells = False
Else 'IsNull(vHasMerged) will be True
ColumnHasMergedCells = True
End If
End If

End Function


RBS


I wish I could attach a sample but I am not sure how to attach a file in
this group. So here goes.

I received an email file with the following setup.

Columns A&B are each individually merged cells. The rows on columns A&B
depend on the group of data and how many records are contained in it
including group summaries and item summaries etc. I do not need anything in
columns A or B for what I am wanting to do. However, if you go to highlight
an area, because of the merged cells you get all this together in the
highlighted area.

Columns C&D have merged cells for the individual items but not the summaries
of the items or the group summaries.

For the item summary and the group summaries, the columns merged together
are columns C though H. Let's say there are three records making up this
item summary. The problem I would like to solve without doing this for 500
record items is to first undo the cell merge formatting in all the above
descibed situations. Then, for Columns C and D once the merged cells are
eliminated, I want to populate the record below that would otherwise be
blank at this point with the information that is in column C and column D.
If there are two records within an item summary, then I need to copy the
column C record down to the next row, but if there are three or more then I
need to copy the column C information down how ever many it takes to fill
out the information for column C. The same thing needs to happen for column
D as described for column c. There would always be an identical copy for
column C and column D information for each item described.

Once all the merged cells are eliminated and the column C and D are properly
populated, then I can go and do a filter to filter out the individual
records I do not want in my new report.

If there is a way to attach files in the google groups so I can better
illustrate, then please let me know how this works.

Thanks