if cell is text move left one column

  • Thread starter Thread starter Saintsman
  • Start date Start date
S

Saintsman

ColB is a long list with sections names followed by category codes
I need to move the text into colA leaving colB with codes only (all numbers)
ColB.
Doors
940590
555998
447006
447008
810697
810705
810706
810707
Windows
619435
525691
525692
 
Try

Sub Macro1()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Not IsNumeric(Range("B" & lngRow)) Then
Range("A" & lngRow).Value = Range("B" & lngRow).Text
Range("B" & lngRow).Value = ""
End If
Next
End Sub
 
Hi,

Try this

Sub Merge()
Set sht = Sheets("Sheet1") ' Change to suit
lastrow = sht.Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set MyRange = sht.Range("B1:B" & lastrow)
For Each c In MyRange
If Not IsNumeric(c.Value) Then
c.Offset(, -1).Value = c.Value
c.ClearContents
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Back
Top