consolidate values to a checklist on sheet 2

[Guest]

Hi,
I have an invoice on sheet 1. There are carton numbers listed in the A
column on random rows. The rows that do not have a carton number are blank
in the A column. How can I create a checklist of the Sheet 1, column A
values on sheet 2 with no blank rows on sheet 2? In addition, some of the
carton numbers are 5 or more digits and I want these separated from the 4 or
less digit carton numbers.
Any suggestions would be much appreciated.

 

[Guest]

You didn't really state how you wanted to seperate the 4 digit codes from 5
digit ones so I have put 4 digit codes in col A on sheet2 and 5 digits in Col
B. This macro should get you started though:

Sub GetLists()

Dim lastRow As Long
Dim colA As Range, Cell As Range
Dim sht1 As Worksheet, sht2 As Worksheet
Dim cnt4 As Long, cnt5 As Long

Set sht1 = Worksheets(1)
Set sht2 = Worksheets(2)

sht1.Activate

lastRow = sht1.Cells(Rows.Count, 1).End(xlUp).Row
Set colA = sht1.Range(Cells(1, 1), Cells(lastRow, 1))

cnt4 = 1
cnt5 = 1

For Each Cell In colA
If Len(Cell.Value) > 4 Then
sht2.Cells(cnt5, 2).Value = Cell.Value
cnt5 = cnt5 + 1
ElseIf Len(Cell.Value) > 0 Then
sht2.Cells(cnt4, 1).Value = Cell.Value
cnt4 = cnt4 + 1
End If
Next Cell

End Sub


HTH
Rowan