Delete row if the merged cell is empty

Question

I have a project that will rearrange the Sheet "GROUP" and format it just like "YTP" sheet. but i dont have an idea how to solve and make it in time. My Problem is how to delete the entire row of YTP sheet if the YTP sheet has an empty row after copying the Data in SHEET GROUP. I have started a code like this:

CODE

Option Explicit
Sub Copy()

  'Declaration for copying the entire column of J
    Dim lastrow As Long, erow As Long
    Dim sRange As Range
    Dim sCrange As Range
    Dim cell As Range
  'End Declare


    Set sRange = Sheet2.Range("J10:J350")
    Set sCrange = Sheet4.Range("J5:J360")

    For Each cell In sRange
        If Not IsEmpty(cell) Then
           sRange.Copy sCrange

        End If
    Next cell
End Sub

This is the Image of Group and YTP sheet

Group

YTP

If you want to see the excel file, Please comment and i will attached it in here. Please Guys, Help me.

Not very clear, but i think you are saying that after you copy you need to delete empty rows, it that is the case see [This](https://stackoverflow.com/questions/9379673/excel-vba-delete-empty-rows?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa); you can also do a search on Google for `delete empty rows excel vba` there are many examples. — GMalc, Apr 25 '18 at 03:22

adhy wijaya · Accepted Answer · 2018-04-25T04:14:25.677

0

Please try this code

Sub Copy()

  'Declaration for copying the entire column of J
    Dim lastrow As Long, erow As Long
    Dim sRange As Range
    Dim sCrange As Range
    Dim sSheet as Worksheet
    Dim sCSheet as Worksheet
    Dim i as Integer
    Dim cell As Range
  'End Declare

    Set sSheet = Worksheets("YTP")
    Set sCSheet = Worksheets("GROUP")
    sSheet.Range("J10:J350").Copy
    sCSheet.Activate
    sCSheet.Range("J5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    lastrow = 360
    For i = 10 to lastrow 
        If sSheet.Range("J" & i).Value = "" then
           sSheet.Rows(i).delete 
           i = i - 1
           lastrow = lastrow - 1  
        End If
        if i > lastrow then Exit For

    Next i

    sSheet.Activate
End Sub

Hope this help.

Thanks

edited Apr 25 '18 at 04:14

answered Apr 25 '18 at 00:47

adhy wijaya

509
3
7

The sheet2 is the Group Sir, and the sheet4 is the ytp. thanks for the answer, Sir, but it does not work. – Apr 25 '18 at 01:20
It works sir, but this wasn't my expected output sir. But you give me an Idea how to :) Thanks a lot sir :) I Hope you can help me with my project :) – Apr 25 '18 at 05:12

Delete row if the merged cell is empty

1 Answers1