Results 1 to 2 of 2

help need to copy and delete rows if condition meets

Threaded View

punar help need to copy and delete... 04-16-2013, 03:38 AM
AB33 Re: help need to copy and... 04-16-2013, 05:08 AM
  1. #1
    Registered User
    Join Date
    02-07-2013
    Location
    cbe
    MS-Off Ver
    Excel 2007
    Posts
    10

    help need to copy and delete rows if condition meets

    Hi Experts,

    I attached the sample file with input and output sheets

    I tried the below code to copy the data from input-item sheet and paste it in the respected output sheets if certain condition meets
    and it is working fine but while i am trying to delete the rows which i copied from input-item the below code is not working fine it only deletes only some of the rows from input and not the all.

    kindly help me to delete the copied rows


    Sub macro()
    Dim ws As Worksheet
    Dim lrow As Long, lcol As Long, i As Long, j As Long, x As Long, y As Long, lastrow As Long
    Application.ScreenUpdating = False
    x = 2
    Set ws = Worksheets("Output - Ignored")
    Set ft = Worksheets("Output - Comments")
    ws.Range("A1:G1").Value = Split("ITEM_CODE,ITEM_NAME,UNIT_NAME,PRICE,ITEM_ID,ignored,Comments", ",")
    With Worksheets("Input - item")
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        For i = 2 To lrow
         For j = 6 To 6
                   
                If .Cells(i, j).Value = "IGNORE" Or .Cells(i, j).Value = "Ignore" Or .Cells(i, j).Value = "ignore" Or .Cells(i, j).Value = "-" Then
                   .Cells(i, j).EntireRow.Copy
                   ws.Cells(x, 1).EntireRow.PasteSpecial
                   x = x + 1
                End If
       
         Next j
        Next i
       
        For i = 2 To lrow
         For j = 6 To 6
          
                If .Cells(i, j).Value = "IGNORE" Or .Cells(i, j).Value = "Ignore" Or .Cells(i, j).Value = "ignore" Or .Cells(i, j).Value = "-" Then
                   .Cells(i, j).EntireRow.Delete
                End If
      
         Next j
        Next i
        ft.Range("A1:G1").Value = Split("ITEM_CODE,ITEM_NAME,UNIT_NAME,PRICE,ITEM_ID,ignored,Comments", ",")
        x = 2
        For i = 1 To lrow
            
          For j = 7 To 7
          
                If .Cells(i, j).Value = "future test" Then
                    .Cells(i, j).EntireRow.Copy
                   ft.Cells(x, 1).EntireRow.PasteSpecial
                   x = x + 1
                   
                End If
          Next j
                        
        Next i
         For i = 1 To lrow
            
          For j = 7 To 7
          
                  If .Cells(i, j).Value = "future test" Then
                   .Cells(i, j).EntireRow.Delete
                End If
          Next j
                        
        Next i
        End With
    MsgBox "Done"
    Application.ScreenUpdating = True
    End Sub

    Thank you in advance for your help.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1