Novice requires macro help

  • Thread starter Thread starter KP
  • Start date Start date
K

KP

I require a macro to sort the following example:

Sheet1
FRED BOB SID BERT
MARY a
SUSAN b c
FAY d e f
JAN g
MARGO h
JO I j


Where the cell has text in it copy the text from the cell along with the
row title and column title and place in a new worksheet.

e.g.

Sheet2
a MARY BOB
b SUSAN FRED
c SUSAN BERT

Hope someone can help!
Thanks is advance,

KP
 
One way to do this is just cycle through the rows and look for non-blank
constants (I assumed that you didn't have formulas!):

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long

Dim myCell As Range
Dim myRng As Range
Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

oRow = 0
With curWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range(.Cells(iRow, 2), _
.Cells(iRow, .Columns.Count)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each myCell In myRng.Cells
oRow = oRow + 1
newWks.Cells(oRow, 1).Value = myCell.Value
newWks.Cells(oRow, 2).Value = .Cells(myCell.Row, 1).Value
newWks.Cells(oRow, 3).Value = .Cells(1, myCell.Column).Value
Next myCell
End If
Next iRow
End With

With newWks
.Range("A1").CurrentRegion.Sort _
key1:=.Range("a1"), order1:=xlAscending, _
key2:=.Range("b1"), order2:=xlAscending, _
key3:=.Range("c1"), order3:=xlAscending, _
header:=xlNo
End With

End Sub
 
Back
Top