VBA Code Assistance

[chryo]

Can anyone throw me a bone here.

I need to write some code to attache to a command button that wil
check an entire spreadsheet and clear all numeric data. Basically th
spreadsheet is for cost analysis and we enter hours. I want to clic
the button and have it clear all the hours from all cells and ignor
any text data.

Any help would be greatly appreciated.

Thanks

 

[Tom Ogilvy]

On error Resume next
Activesheet.Cells.Specialcells(xlConstants,xlNumbers).Clearcontents
On Error goto 0

 

[JE McGimpsey]

One way:

Public Sub ClearNumbers()
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
On Error GoTo 0
End Sub

 

[Dave Peterson]

How about recording a macro when you select your range.
then edit|goto special
check Constants and just numbers
then click ok

then hit the delete key (to clear contents)

I got this when I did it:

Range("A7:E20").Select
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.ClearContents

You could modify it to:

on error resume next
Range("A7:E20").SpecialCells(xlCellTypeConstants, 1).ClearContents
on error goto 0

(on error resume next means that the code won't blow up if there are no constant
numbers.)

(And I'd try to limit my range--there may be some stuff you want to keep--like
dates/headers.)

 

[Bob Flanagan]

Chryo, there is a problem with the specialcells approach in the prior
suggestions. A cell with a formula like = 4 or =5+9 is not picked up as an
input cell. The specialcells would only pick up entries like 33, 44, etc,
that are not entered with an equal sign. The only approach I have found to
do what you want is to check numeric cells cell by cell and if they do not
contain any letters, then they are input cells. The routine below is an
approach on how to do.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Sub ClearNumbers()
Dim numCells As Range
Dim formulaCells As Range
Dim cellsToClear As Range
On Error Resume Next
'get numeric cells without equal sign at start
Set numCells = ActiveSheet.Cells.SpecialCells(xlConstants, xlNumbers)
'get all cells with an equal sign at start
Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
'exit if no matching cells
If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub
'concationate the two selections into one
If numCells Is Nothing Then
Set inputcells = formulaCells
ElseIf formulaCells Is Nothing Then
Set inputcells = formulaCells
Else
Set inputcells = Union(formulaCells, numCells)
End If
'check for entries w/o a, b, c... which would be a non
'input cell
For Each cell In inputcells
If bNumber(cell.Formula) Then
If cellsToClear Is Nothing Then
Set cellsToClear = cell
Else
Set cellsToClear = Union(cell, cellsToClear)
End If
End If
Next
'if no input cells, exit
If cellsToClear Is Nothing Then Exit Sub
'set input cells to zero
cellsToClear.Value = 0
End Sub

Private Function bNumber(cellFormula As String) As Boolean
'return false if cells contain a letter
Dim I As Integer
cellFormula = UCase(cellFormula)
For I = 65 To 90
If InStr(cellFormula, Chr(I)) > 0 Then Exit Function
Next
bNumber = True
End Function

 

[Tom Ogilvy]

Just some observations in addition to your own:
your bNumber function is a waste of time since formulaCells and numCells
only include cells that would pass the IsNumber test.

Also, there are many other characters besides upper or lowercase characters
that would make the cell non numeric - so the check is flawed as well.

In any event, it appears unlikely that the user, if entering time values, is
entering things like =0.6532134576

--
Regards,
Tom Ogilvy

Chryo, there is a problem with the specialcells approach in the prior
suggestions. A cell with a formula like = 4 or =5+9 is not picked up as an
input cell. The specialcells would only pick up entries like 33, 44, etc,
that are not entered with an equal sign. The only approach I have found to
do what you want is to check numeric cells cell by cell and if they do not
contain any letters, then they are input cells. The routine below is an
approach on how to do.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Sub ClearNumbers()
Dim numCells As Range
Dim formulaCells As Range
Dim cellsToClear As Range
On Error Resume Next
'get numeric cells without equal sign at start
Set numCells = ActiveSheet.Cells.SpecialCells(xlConstants, xlNumbers)
'get all cells with an equal sign at start
Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
'exit if no matching cells
If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub
'concationate the two selections into one
If numCells Is Nothing Then
Set inputcells = formulaCells
ElseIf formulaCells Is Nothing Then
Set inputcells = formulaCells
Else
Set inputcells = Union(formulaCells, numCells)
End If
'check for entries w/o a, b, c... which would be a non
'input cell
For Each cell In inputcells
If bNumber(cell.Formula) Then
If cellsToClear Is Nothing Then
Set cellsToClear = cell
Else
Set cellsToClear = Union(cell, cellsToClear)
End If
End If
Next
'if no input cells, exit
If cellsToClear Is Nothing Then Exit Sub
'set input cells to zero
cellsToClear.Value = 0
End Sub

Private Function bNumber(cellFormula As String) As Boolean
'return false if cells contain a letter
Dim I As Integer
cellFormula = UCase(cellFormula)
For I = 65 To 90
If InStr(cellFormula, Chr(I)) > 0 Then Exit Function
Next
bNumber = True
End Function

 

[Bob Flanagan]

Tom, the use of special cells at the start selects only the numeric cells.
The intention of the bNumber function is to exclude from those numeric cells
the cells that are true formulas, such as =A1+B1 and return true when there
is no letter and thus a true number input. Thus it is not flawed or a waste
of time. The intent is not to refilter to exclude numeric cells.
Characters that change a number to a non numeric are not relevant and
filtered out at the beginning by the use of specialcells. Lastly, the user
said he was entering hours, not time. An hour entry can be a number like 3,
or an entry =4 + 45/60. How would you suggest he identify and clear entries
like = 4 + 45/60 ?

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Just some observations in addition to your own:
your bNumber function is a waste of time since formulaCells and numCells
only include cells that would pass the IsNumber test.

Also, there are many other characters besides upper or lowercase characters
that would make the cell non numeric - so the check is flawed as well.

In any event, it appears unlikely that the user, if entering time values, is
entering things like =0.6532134576

--
Regards,
Tom Ogilvy

Chryo, there is a problem with the specialcells approach in the prior
suggestions. A cell with a formula like = 4 or =5+9 is not picked up as an
input cell. The specialcells would only pick up entries like 33, 44, etc,
that are not entered with an equal sign. The only approach I have found to
do what you want is to check numeric cells cell by cell and if they do not
contain any letters, then they are input cells. The routine below is an
approach on how to do.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Sub ClearNumbers()
Dim numCells As Range
Dim formulaCells As Range
Dim cellsToClear As Range
On Error Resume Next
'get numeric cells without equal sign at start
Set numCells = ActiveSheet.Cells.SpecialCells(xlConstants, xlNumbers)
'get all cells with an equal sign at start
Set formulaCells =

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas,

 

[JE McGimpsey]

Note that you could eliminate the whole expensive bNumber looping
routine by replacing

if bNumber(cell.Formula) Then

with

if Not cell.Formula Like "*[A-Za-z]*" Then


and a real niggle:

If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub
'concationate the two selections into one
If numCells Is Nothing Then
Set inputcells = formulaCells
ElseIf formulaCells Is Nothing Then
Set inputcells = formulaCells
Else
Set inputcells = Union(formulaCells, numCells)
End If

would be a bit more efficient as

If numCells Is Nothing Then
If formulaCells Is Nothing Then
Exit Sub
Else
Set inputcells = formulaCells
End If
Elseif formulaCells Is Nothing Then
Set inputcells = numCells
Else
Set inputcells = Union(numCells, formulaCells)

End If

Since numCells and formulaCells have to be evaluated exactly once in
each branch, as opposed to at least one of them, and possibly both,
evaluated twice.





Note also that this is a bit misleading, though irrelevant to the code:

'get all cells with an equal sign at start
Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)

The code actually gets only cells with numeric formulae, not "all cells
with an equal sign at start".

 

[Bob Flanagan]

Nice upgrades!

Bob

Note that you could eliminate the whole expensive bNumber looping
routine by replacing

if bNumber(cell.Formula) Then

with

if Not cell.Formula Like "*[A-Za-z]*" Then


and a real niggle:

If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub
'concationate the two selections into one
If numCells Is Nothing Then
Set inputcells = formulaCells
ElseIf formulaCells Is Nothing Then
Set inputcells = formulaCells
Else
Set inputcells = Union(formulaCells, numCells)
End If

would be a bit more efficient as

If numCells Is Nothing Then
If formulaCells Is Nothing Then
Exit Sub
Else
Set inputcells = formulaCells
End If
Elseif formulaCells Is Nothing Then
Set inputcells = numCells
Else
Set inputcells = Union(numCells, formulaCells)

End If

Since numCells and formulaCells have to be evaluated exactly once in
each branch, as opposed to at least one of them, and possibly both,
evaluated twice.





Note also that this is a bit misleading, though irrelevant to the code:

'get all cells with an equal sign at start
Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)

The code actually gets only cells with numeric formulae, not "all cells
with an equal sign at start".

Chryo, there is a problem with the specialcells approach in the prior
suggestions.

 

[Dave Peterson]

Another option if you're worried about the user typing formulas into the input
cells.

Select all the cells that can be used for data entry and give it a nice range
name (Insert|name|define).

Then just:

worksheets("inputsheetname").range("inputrng1").clearcontents

Then you won't have to worry about how the user types his/her entries.