Remove blank rows from range

[sharky12345]

I'm using this to sort data into different columns depending on a cell value;

Sheet9.Range("P2:T40").ClearContents
For Each Cell In Sheet9.Range("E2:E28")
If Cell <> "" Then
If Cell.Offset(0, 3) = "N" Then
Cell.Offset(0, 11).Value = Cell
Cell.Offset(0, 12).Value = Cell.Offset(0, 1).Value
End If
If Cell.Offset(0, 3) = "Y" Then
Cell.Offset(0, 14).Value = Cell
Cell.Offset(0, 15).Value = Cell.Offset(0, 1).Value
End If
End If
Next

The issue I have is that it it leaving blank rows in the sorted data because the cells housing "N" or "Y" are not contiguous.

I know why it's doing it, just not how to resolve it if anyone can help?

 

[Osvaldo Palmeiro]

Try:

Sub Test()
 Dim k  As Long
  For k = 28 To 2 Step -2
   If Cells(k, 5) <> "" Then
    If Cells(k, 8) = "N" Then
     Cells(k, 16) = Cells(k, 5)
     Cells(k, 17) = Cells(k, 6)
    ElseIf Cells(k, 8) = "Y" Then
     Cells(k, 19) = Cells(k, 5)
     Cells(k, 20) = Cells(k, 5)
    Else: Rows(k).Delete
    End If
   End If
  Next
End Sub

 

[sharky12345]

Hello, and thanks - this doesn't work because it's still leaving blank rows between each of the sorted values.

 

[MARK858]

Can we have some before and after screenshots of your data please? also do your cells contain constants or formulas?

 

[Osvaldo Palmeiro]

Try this then.

Sub Test()
 Dim k  As Long
  For k = 28 To 2 Step -2
   If Cells(k, 5) <> "" Then
    If Cells(k, 8) = "N" Then
     Cells(k, 16) = Cells(k, 5)
     Cells(k, 17) = Cells(k, 6)
    ElseIf Cells(k, 8) = "Y" Then
     Cells(k, 19) = Cells(k, 5)
     Cells(k, 20) = Cells(k, 5)
    Else: Rows(k).Delete
    End If
   [COLOR=#ff0000]Else: Rows(k).Delete[/COLOR]
   End If
  Next
End Sub

 

[sharky12345]

Guys - thanks, and sorry for the delay in responding.

The 2nd suggestion by Osvaldo works, so thanks to both.