Public Sub NAddr2SS() 'Convert 1-Up Name and Address labels to Spread Sheet format. 'David McRitchie http://www.mvps.org/dmcritchie/excel/excel.htm ' 1999-03-01 http://www.mvps.org/dmcritchie/excel/naddr2ss.txt Dim nCol As Long, nRow As Long Dim cRow As Long Dim lastrow As Long Dim wsSource As Worksheet Dim wsNew As Worksheet nCol = 0 nRow = 1 Set lastcell = cells.SpecialCells(xlLastCell) lastrow = lastcell.Row Set wsSource = ActiveSheet Set wsNew = Worksheets.Add Application.ScreenUpdating = False Application.DisplayAlerts = False For cRow = 1 To lastrow If Trim(wsSource.cells(cRow, 1).Value) = "" Then If nCol > 0 Then nRow = nRow + 1 nCol = 0 Else nCol = nCol + 1 wsNew.cells(nRow, nCol).Value = wsSource.cells(cRow, 1).Value End If Next cRow Application.ScreenUpdating = True 'place at end when debugged Application.DisplayAlerts = True End Sub Public Sub NAddr2SS4() 'Convert 1-Up Name and Address labels to Spread Sheet format. 'David McRitchie http://www.mvps.org/dmcritchie/excel/excel.htm ' 1999-03-01 http://www.mvps.org/dmcritchie/excel/naddr2ss.txt ' 2001-01-25 as NAddr2SS4 to insure 4 columns Dim nCol As Long, nRow As Long, cRow As Long, lastrow As Long Dim insureCol As Long Dim wsSource As Worksheet, wsNew As Worksheet Dim lastcell As Range nCol = 0 nRow = 1 insureCol = 4 'insure this column is filled in... Set lastcell = Cells.SpecialCells(xlLastCell) lastrow = lastcell.Row + 1 'adjustment to help with insureCol Set wsSource = ActiveSheet Set wsNew = Worksheets.Add Application.ScreenUpdating = False Application.DisplayAlerts = False For cRow = 1 To lastrow If Trim(wsSource.Cells(cRow, 1).Value) = "" Then If nCol > 0 Then If nCol < insureCol Then wsNew.Cells(nRow, insureCol).Value = _ wsNew.Cells(nRow, nCol).Value wsNew.Cells(nRow, nCol).Value = "" End If End If nRow = nRow + 1 nCol = 0 Else nCol = nCol + 1 wsNew.Cells(nRow, nCol).Value = wsSource.Cells(cRow, 1).Value End If Next cRow Cells.EntireColumn.AutoFit Application.ScreenUpdating = True 'place at end when debugged Application.DisplayAlerts = True End Sub