Solved: Cleaning up a list.... direction please.

[Papadopoulos]

Ok, in theory this should be really easy but it bumps up against a gap in my VBA knowledge. On one sheet I am working up an estimate. The items in the estimate end up in a list regardless of whether they have values or not. That's fine on what is essentially a worksheet. The quote however needs to be tidier.
I would like to transfer only the items who's description is not equal to none.
in the sample, Sheet 1 has the data that would typically be on the worksheet. Sheet 2 has the data as it currently appears at the top and what I would like it to look like at the bottom. If I do this like everything else in this app it will consist of a very long cumbersome If/Else If sequence so I have just left it out. I would think that it would be a matter of testing a range (not equal to None) and then storing that in a variable... increment the row by one...
Sorry, this is beyond me, at least at this moment. I will even take a link to something similar and try an decifer it myself. (Not trying to get someone to do this for me just looking for a sensible direction to head toward)

Thanks,
David

[Bob Phillips]

I don't get it. Which set of data do you want to process, and what do you want it to end up as?

[Tinbendr]

I'm not sure how you get the heading but I'm sure you can add that.

Sub SubForm()

Dim WB As Workbook
Dim WS As Worksheet
Dim WS2 As Worksheet
Dim aRow As Range
Dim lCount As Long
Dim LastRow As Long
Dim DestRow As Long

DestRow = 23 'Start at this row at destination.

Set WB = ActiveWorkbook
Set WS = WB.Sheets(1)
Set WS2 = WB.Sheets(2)

LastRow = WS.Range("F65536").End(xlUp).Row

For Each aRow In WS.Range("F8:F" & LastRow)
Select Case UCase(aRow)

Case "NONE", ""

Case "ADD DESCRIP"
If aRow.Offset(0, -2) <> 0 Then
With WS2
.Range("D" & DestRow + lCount).Value = aRow.Value
.Range("J" & DestRow + lCount).Value = aRow.Offset(0, -2).Value
lCount = lCount + 1
End With
End If
Case Else
With WS2
.Range("D" & DestRow + lCount).Value = aRow.Value
.Range("J" & DestRow + lCount).Value = aRow.Offset(0, -2).Value
lCount = lCount + 1
End With
End Select
Next

End Sub

[Papadopoulos]

To xld: Sheet 1 contains what I am essentially starting with.
The upper portion of sheet 2 is what I would currently display - I am not weeding out the "NONE" entries just referencing cells on the sheet 1. The lower portion of sheet 2 is what I am trying to get to.

To Tinbendr: I will give this a try over the weekend. Just getting back from class and have to be back tomorrow morning early. (I am teaching a basic rider course over this weekend - motorcycles)

Thanks to all.

David