Moving and/or deleting blank rows

[PerryK]

Hello everyone I have solved all of my problems thus far thanks to your help and now I have one last issue on my current problem that I didn't think about until just now. The current code cuts rows that have been completed or placed on hold from sheet 1 to sheet 2 (complete) or sheet 3 (hold). On Sheet 1 how do I move the blank rows either to the bottom or delete them so that there are no gaps in the data? Thank you very much in advance.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 11 Then
      Cancel = True
      Target.Offset(, 2).Value = "IN PROGRESS"
      Target.Offset(, 4).Value = Time
   ElseIf Target.Column = 12 Then
      Cancel = True
      Application.EnableEvents = False
      Target.Offset(, 1).Value = "COMPLETE"
      Application.EnableEvents = True
      Target.Offset(, 4).Value = Time
      Target.EntireRow.Cut
      Sheet2.Range("A5").EntireRow.Insert xlShiftDown
      Target.EntireRow.Delete
   ElseIf Target.Column = 14 Then
      Cancel = True
      Target.Offset(, -1).Value = "PARTIAL HOLD"
   End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
    If Not Intersect(Target, Range("M:M")) Is Nothing Then
        If UCase(Target.Value) = "COMPLETE" Then


        ElseIf UCase(Target.Value) = "PARTIAL HOLD" Then
            Target.EntireRow.Cut
            Sheet3.Range("A5").EntireRow.Insert xlShiftDown
            Target.EntireRow.Delete
        End If
    End If
End Sub

 

[mumps]

Place this line of code in the appropriate location in your macro:

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[Fluff]

This will delete all rows where col A is bank

Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete

 

[PerryK]

Thank you both. Both codes you have provided work, but they work only once. Is there a way to make it work every time or did I place it in the wrong spot in the code I already have? Please advice.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
    If Not Intersect(Target, Range("M:M")) Is Nothing Then
        If UCase(Target.Value) = "COMPLETE" Then
            
        ElseIf UCase(Target.Value) = "PARTIAL HOLD" Then
            Target.EntireRow.Cut
            Sheet3.Range("A5").EntireRow.Insert xlShiftDown
            Target.EntireRow.Delete
            columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'I placed both codes separately at this line when testing each'
        End If
    End If
End Sub

 

[mumps]

The code will run every time there is a change in column M. The first time it runs, all blank rows will be deleted. Each time it runs after that, if somehow more blank rows were added, they will also be deleted.

 

[PerryK]

OK I got it placed properly in the code now thank you very much

 

[mumps]

Glad we could help.

 

[smittymj]

Hi! I'm hoping to build a similar code to the above and I'm hoping I could get some help as I don't really understand the code above. Somewhere along the line that:

1. In Sheet 1, if any cells in Column E is marked as "Done" it would cut Column C of that row to the last data in the row
2. Paste values into the next free row in Sheet2
3. Then delete the now blank row (entire row) in Sheet 1

Hoping anyone can help out!

 

[mumps]

@smittymj: Welcome to the Forum. It is against Forum rules to post your question in another person's thread. Please start your own new thread and include a link to this thread of you feel it is helpful. Please review the rules at this link: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
If you send me a private message with the link to your new thread, I will have a look at it.

 

[smittymj]

@smittymj: Welcome to the Forum. It is against Forum rules to post your question in another person's thread. Please start your own new thread and include a link to this thread of you feel it is helpful. Please review the rules at this link: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
If you send me a private message with the link to your new thread, I will have a look at it.

Oof, I'm really sorry! Will do :D