Results 1 to 10 of 10

Thread: help adjusting code delete empty rows based on condition from sheet1 to sheet2

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    help adjusting code delete empty rows based on condition from sheet1 to sheet2

    hello
    i would some help about adjusting code when transfer data of invoice from sheet1 to sheet2 the sheet1 contains some empty rows when i transfer i don't want transfer the empty rows to sheet2 only filling the data in sheet1 begins from range a3:e31 the empty rows should delete from a19:e30 if the cells a19: a30= empty i make attention the range from a19:e30 don't delete in sheet1 because i need them i don't want in sheet2 when i transfer and here the notes i would a31: e31 connect with the last filling row value for instance the last filling row value in range a22:e22 =200
    then the a31:e31=200 i know it this complicated qustion i hope this help to solve my problem and this is my code :


    HTML Code:
    Sub TransferData()
    Dim x As Integer
    
        Dim Target As Range 
       Dim LastRow As Long   
     Dim R As Long
    For x = 30 To 19 Step -1   
     If sheet2.Range("A" & x).Value = vbNullString Then 
    sheet2.Range("A" & x).EntireRow.Delete  
      Next x    
    LastRow = sheet1.Cells(.Rows.Count, "A").End(xlUp).Row  
      Set Target = sheet2.Cells(sheet2.Rows.Count, "A").End(xlUp)   
     For R = 2 To LastRow      
     sheet1 .Range(sheet1.Cells(R, 1), sheet1.Cells(R, 5)).Copy _                
    Destination:=Target.Offset(R - 2)        With Target.Offset(R - 2, 4)          
      If .HasFormula Then .Value =sheet1 .Cells(R, 5).Value     
       End With         
       Next R
        On Error Resume Next                  
      Target.Offset(R - 3, 5).Value = WorksheetFunction.Sum(sheet2.Range("E3:E35"))End Sub
    Last edited by maghari; 10-21-2019 at 03:47 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •