If and elseif

[Doc203]

I am struggling to get this right -

This looks to see if the contents of a cell begin with a letter - if it is a letter then delete the cell and shift left. It loops until it finds a blank cell. I need it to repeat the process until the cell begins with a number.

This code works, but only checks each cell once and then moves to the next cell:

Dim irow As Integer
    irow = 2
    Do While Len(Cells(irow, 2)) > 0
      If Val(Left(Cells(irow, 2), 1)) = IsText Then
        Cells(irow, 2).Delete Shift:=xlShiftToLeft
      End If
      irow = irow + 1
    Loop

I have tried this code and it works on the 1st row, but will not go to the next row and hangs:

Dim irow As Integer
    irow = 2
    Do While Len(Cells(irow, 2)) > 0
      If Val(Left(Cells(irow, 2), 1)) = IsText Then
        Cells(irow, 2).Delete Shift:=xlShiftToLeft
        Else: End
      End If
      irow = irow + 1
    Loop

I am sure I need an "ISERROR" or something, but I cannot figure it out. Thanks for any help.

 

[Blade Hunter]

It's because this is not a valid function:

= IsText

It is comparing to a variable called IsText which will always be false.

Change it to this:

If Application.WorksheetFunction.IsText(Val(Left(Cells(irow, 2), 1))) Then

Cheers

Dan

 

[Doc203]

Thanks for the reply - it returns "Argument not optional" with the following code:

Dim irow As Integer
    irow = 2
    Do While Len(Cells(irow, 2)) > 0
      If Val(Left(Cells(irow, 2), 1)) = Application.WorksheetFunction.IsText Then
        Cells(irow, 2).Delete Shift:=xlShiftToLeft
      End If
      irow = irow + 1
    Loop

 

[Doc203]

Ok, re-read it and changed it to:

Dim irow As Integer
    irow = 2
    Do While Len(Cells(irow, 2)) > 0
      If Application.WorksheetFunction.IsText(Val(Left(Cells(irow, 2), 1))) Then
        Cells(irow, 2).Delete Shift:=xlShiftToLeft
      End If
      irow = irow + 1
    Loop

No good, I will keep trying

 

[Doc203]

It will run the original way that I had it, I just would like to get it to keep deleting until it finds that the cell begins with a number and move to the next one.

I can accomplish this by re-running the macro 4 or 5 times, but I would like to put it into a loop of some type.

 

[John Davis]

It will run the original way that I had it, I just would like to get it to keep deleting until it finds that the cell begins with a number and move to the next one.

I can accomplish this by re-running the macro 4 or 5 times, but I would like to put it into a loop of some type.

Does this work?

Dim irow As Integer
    irow = 2
    Do While Len(Cells(irow, 2)) > 0
      If IsText(Val(Left(Cells(irow, 2), 1))) = True Then Cells(irow, 2).Delete Shift:=xlToLeft
      If IsNumeric(Val(Left(Cells(irow, 2), 1))) = True Then exit sub
      irow = irow + 1
    Loop

 

[Doc203]

Thanks for the reply John - I got it to work with this, but I like seeing different options...

    Dim irow As Integer
    irow = 2
    Do While Len(Cells(irow, 2)) > 0
      Do While Val(Left(Cells(irow, 2), 1)) = IsText
        Cells(irow, 2).Delete Shift:=xlShiftToLeft
      Loop
      irow = irow + 1
    Loop