MrExcel Publishing
Your One Stop for Excel Tips & Solutions

For...Next trouble


Posted by Cory on July 24, 2001 10:59 AM

I have a column of data that begins with text in cell A1 followed by a number in A2. This pattern continues down the column for about 500 rows: text, number, text, number...
I was trying to make a button on the sheet that goes through the column, finds the cells with numeric values and deletes their corresponding ROWS. In this case it would delete every other row. I think my cValue variable is what's hanging me up, but I can't figure out another way to make it find cells with only numeric data:

Private Sub CommandButton1_Click()
Range("A1").Activate

Dim myRow As Integer
Dim myCol As Integer
Dim Counter As Integer
Counter = 0
myCol = ActiveCell.Column
cValue = IsNumeric(ActiveCell.Value)
rBegin = 1
rEnd = 20

For myRow = rEnd To rBegin Step -1
Application.StatusBar = Counter & " rows deleted."
If Cells(myRow, myCol).Value = cValue Then
Range(Cells(myRow, myCol)).EntireRow.Delete
Counter = Counter + 1
End If
Next myRow
Application.StatusBar = False

End Sub

Any ideas would be awesome!

Thanks,
Cory


Posted by faster on July 24, 2001 11:32 AM


'try this
Sub DelNum()
Range("A1").Select
Do While Selection <> ""
If Application.WorksheetFunction.IsNumber(ActiveCell) = True Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop
Range("A1").Select
End Sub

Posted by Cory on July 24, 2001 2:59 PM

faster!! You rule!!

I was totally looking in the wrong direction. I'm just now trying to learn For...Next and Do...Loop statements and your example helped a lot! Thanks

Cory