PDA

View Full Version : Checking Columns for Numbers



Drafter
11-28-2011, 07:18 AM
Hi! Everyone- I am trying to write some syntax that searches a spread sheet (excel) in coloumn A for a number -- if there is no number then deletes the row. I am currently trying to do it with isNumeric-- FAIL! Can anyone give me better idea?

mikerickson
11-28-2011, 07:34 AM
What is the code that fails?
Are you looping from bottom to top?

Dim LastRow as Long
Dim i as Long

With ThisWorkbook.Sheet("Sheet1")
LastRow = .Cells(.Rows.Count, 1).End(xlup).Row

For i = LastRow To 1 Step - 1
If Not IsNumeric(CStr(.Cells(i, 1).Value)) Then
.Rows(i).EntireRow.Delete
End If
Next i
End With

Bob Phillips
11-28-2011, 07:34 AM
Sub ProcessRows()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With Activesheeet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = Lastrow To 2 Step -1

If Not IsNumeric(.Cells(i, "A").Value) Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

mikerickson
11-28-2011, 07:35 AM
:)

Drafter
11-28-2011, 07:38 AM
Option Explicit
Sub CSV_FORMAT2()
'
' CSV_FORMAT2 Macro
' FORMATS THE CVS FROM ASBUILT FILES
'
' Keyboard Shortcut: Ctrl+Shift+W
Dim Owb As Workbook
Dim Ows As Worksheet
Dim ValA As String, Pend As String
Dim r As Long
Set Ows = ThisWorkbook.Worksheets(1)
Pend = InputBox("Enter phase AB for asbuilt pr for prelim stk for staking and loop number", "Phase and Loop", "-AB-317")
For r = 1 To Ows.UsedRange.Rows.Count
ValA = Ows.Cells(r, 1).Value

If IsNumeric(ValA) = False Then
Rows.Delete
ElseIf IsNumeric(ValA) = True Then
ValA = ValA & Pend
Ows.Cells(r, 1) = ValA

End If
Next r
MsgBox "Done"
End Sub

Drafter
11-28-2011, 07:39 AM
It ends up clearing the entire spreadsheet



Option Explicit
Sub CSV_FORMAT2()
'
' CSV_FORMAT2 Macro
' FORMATS THE CVS FROM ASBUILT FILES
'
' Keyboard Shortcut: Ctrl+Shift+W
Dim Owb As Workbook
Dim Ows As Worksheet
Dim ValA As String, Pend As String
Dim r As Long
Set Ows = ThisWorkbook.Worksheets(1)
Pend = InputBox("Enter phase AB for asbuilt pr for prelim stk for staking and loop number", "Phase and Loop", "-AB-317")
For r = 1 To Ows.UsedRange.Rows.Count
ValA = Ows.Cells(r, 1).Value

If IsNumeric(ValA) = False Then
Rows.Delete
ElseIf IsNumeric(ValA) = True Then
ValA = ValA & Pend
Ows.Cells(r, 1) = ValA

End If
Next r
MsgBox "Done"
End Sub

mikerickson
11-28-2011, 07:51 AM
The Rows.Delete is what clears all the spreadsheet. (I'm surprised it doesnt crash).

cf. Rows(i).Delete in xld's and my code which deletes a specific row.

Note also that the failing code loops from top to bottom.

Drafter
11-28-2011, 07:53 AM
Thank you!

Bob Phillips
11-28-2011, 12:35 PM
:)

To the minute :)