Search text on merge cells and hide row for blank cells

[francozzy]

Hi,

I have some table as sample for the real table issue like below :

i would like copy the whole tables into new worksheet,
and then split the two table split into two sheet,
let say split into table A(first table) and table B(second table)

After copying and splitting, i want to search for non blank cell on table A start the third row from column H to V,
if i find blank cell, it will follow by hide the row with the blank cell
and table A will be show like below:

and same as for table B,
but start from the fourth row from column H to V and will show like below :

I want to make all the step using Macro,
Is that possible to the reseult make like as table A & B ?
cause i'm stuck on search text in merge cell and hide the table row if found blank cell within merge cell

FYI, for the source table cannot be changed how to display, coz it's already presented for the whole day in a month into one sheet, and each sheet represent each month within a year,
So total will be 12 sheets on one worksheet.

Anyone can help ?

Thank You.

 

[francozzy]

anyone can help ??

Please

Thank You.

 

[Yongle]

@francozzy Please test for me (I will not be putting formula in final solution - just want to test if the cells are truly empty etc)

Place this formula in W3 and copy down

=IF(COUNTA(H3:V3)=0,"HIDE","")

Are the correct rows marked with "HIDE"

 

[Yongle]

i would like copy the whole tables into new workSHEET,
and then split the two table split into two sheet,
let say split into table A(first table) and table B(second table)

new workSHEET or new workBOOK ?

 

[Yongle]

Does this copy the sheet correctly for you?

Paste code into standard module
Select the sheet to be copied
Run the code
Select a cell when told to do so

Sub copySheet()
    Dim a As Long, Cel As Range, newBook As Workbook
    Set Cel = Application.InputBox("select first cell of table 2 and click ok", , , , , , , 8)
    Set Cel = ActiveSheet.Cells(Cel.Row, 1)
'add workbook
    Set newBook = Workbooks.Add
'copy sheets twice
    With Cel.Parent
        For a = 1 To 2
            .Copy Before:=newBook.Sheets(1)
        Next
    End With
'delete table 1
    With newBook.Sheets(1)
        .Range("A1").Resize(Cel.Row - 1).EntireRow.Delete
    End With
'delete table 2
    With newBook.Sheets(2)
        .Range(Cel.Address, .Cells(Rows.Count, 1)).EntireRow.Delete
    End With
End Sub