Results 1 to 3 of 3

Thread: VBA - Delete row based on cell above (date) and cells left (number)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2014
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Delete row based on cell above (date) and cells left (number)

    Hi - I have in A some order numbers and D has dates. A will populate blanks below row 1 until a new order begins and then enter a new order number followed by blanks dependent on how many lines on the order. D has 2 different dates.

    I want to be able auto fill the blanks in A (already solved) and delete the whole row based on the date above not matching and the order numbers matching as well.
    Where the order number is listed as default (before auto fill) the corresponding date will always be the one to keep

    Sorry i cant post attachements!

    1 .......order number..........code............description.......date.........result
    2 .......66644...................Item1 ............Descr 1 ........13/06 .......Keep
    3 .......blank ...................item2 ............descr 2 ........13/06 .......keep
    4 .......blank ...................item 3 ............descr 3 ........07/06 .......delete
    5 .......blank ...................item 4 ............descr 4 ........13/06 .......keep
    6 .......66687 .................Item1 ............Descr 1 ........20/06 .......Keep
    7 .......blank ..................item5 ............descr 5 ........13/06 .......delete
    8 .......blank ...................item 6 ............descr 6 ........20/06 .......keep
    9 .......blank ...................item 7 ............descr 7 ........13/06 .......delete
    10 .....67015 ...................item 3 ............descr 3 ........19/09 .......keep
    Last edited by marshy3300; Jun 20th, 2019 at 10:48 PM.

  2. #2
    Board Regular ParamRay's Avatar
    Join Date
    Aug 2014
    Location
    England, UK
    Posts
    1,184
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Delete row based on cell above (date) and cells left (number)

    Here is how I did it. Make sure you change the sheet name in the indicated line.

    Code:
    Public Sub DeleteRows()
      Dim lngLastRow As Long
      Dim lngCounter As Long
      Dim rngFirst As Range
      Dim wksData As Worksheet
      Dim j As Long
      
      On Error GoTo ErrorHandler
      Set wksData = ThisWorkbook.Sheets("Orders")   '<--- IMPORTANT: Set name of sheet here
      
      lngLastRow = wksData.Cells(wksData.Rows.Count, "B").End(xlUp).Row
    
    ' Fill the blanks in column A:
      
      On Error Resume Next
      With wksData.Range("A2:A" & lngLastRow)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
      End With
      
    ' Delete rows with inconsistent dates:
      
      On Error GoTo ErrorHandler
      For j = lngLastRow To 2 Step -1
        Set rngFirst = wksData.Range("A2:A" & lngLastRow).Find( _
          What:=wksData.Cells(j, "A").Value, _
          After:=wksData.Cells(lngLastRow, "A"), _
          LookIn:=xlValues, LookAt:=xlWhole)
        If wksData.Cells(j, "D").Value <> rngFirst.Offset(, 3).Value Then
          wksData.Rows(j).Delete
          lngCounter = lngCounter + 1
        End If
      Next j
      
      MsgBox Format(lngCounter, "#,0") & " row(s) were deleted.", vbInformation
      
    ExitHandler:
      Set rngFirst = Nothing
      Set wksData = Nothing
      Exit Sub
      
    ErrorHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub
    Windows 10, Excel 365

  3. #3
    Board Regular
    Join Date
    Aug 2014
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Delete row based on cell above (date) and cells left (number)

    Hi ParamRay - You're a gun. I like how you've incorporated the solved bit as well.
    Works a treat - Cheers

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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