Delete Data in Range -macro

[ra]

Hello,

I want to delete data (entire row) within a certain range e.g. between
10% to 20%.

My first cut at it is below but seems to delete everything! any help
would be appreciated.

Dim StRange As Integer
Dim FinRange As Integer, LastRow&, i&
Dim ColumnNum As Integer
ColumnNum = InputBox("Column NUMBER with Dates of Interest (e.g.
A=1)")
StRange = InputBox("Values to be Deleted-ENTER Start Range %
(0.00)")
FinRange = InputBox("Values to be Deleted-ENTER End Range % (0.00)")
LastRow = Cells(Rows.Count, ColumnNum).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, ColumnNum).Value >= StRange And _
Cells(i, ColumnNum).Value <= FinRange Then
Rows(i).Delete
End If
Next i
End Sub

 

[Guest]

I would add

FinRange = InputBox("Values to be Deleted-ENTER End Range % (0.00)")
msgbox stRange & " - " & FinRange & " - " & ColumnNum

and see how they compare to the values in your column.

 

[JE McGimpsey]

One way:

Const cdMaxPercent As Double = 1
Const csMsgColNum As String = _
"Column Number with Dates of interest (e.g., A=1)"
Const csMsgMin As String = _
"Minimum Value to be deleted (in %)"
Const csMsgMax As String = _
"Maximum Value to be deleted (in %)"
Const csTitle As String = "Delete Value Range"
Dim vResult As Variant
Dim rDelete As Range
Dim nCol As Long
Dim i As Long
Dim dMin As Double
Dim dMax As Double

Do
vResult = Application.InputBox( _
Prompt:=csMsgColNum, _
Title:=csTitle, _
Type:=1, _
Default:=1)
If vResult = False Then Exit Sub 'user cancelled
nCol = CLng(vResult)
Loop Until nCol > 0 And nCol <= ActiveSheet.UsedRange.Columns.Count
Do
vResult = Application.InputBox( _
Prompt:=csMsgMin, _
Title:=csTitle, _
Type:=1, _
Default:=Format(0, "0.00%"))
If vResult = False Then Exit Sub 'user cancelled
dMin = CDbl(vResult)
Loop Until dMin >= 0 And dMin < cdMaxPercent
Do
vResult = Application.InputBox( _
Prompt:=csMsgMax, _
Title:=csTitle, _
Type:=1, _
Default:=Format(cdMaxPercent, "0.00%"))
If vResult = False Then Exit Sub 'user cancelled
dMax = CDbl(vResult)
Loop Until dMax >= dMin And dMax <= cdMaxPercent
For i = 2 To Cells(Rows.Count, nCol).End(xlUp).Row
With Cells(i, nCol)
If .Value >= dMin And .Value <= dMax Then
If rDelete Is Nothing Then
Set rDelete = .Cells
Else
Set rDelete = Union(rDelete, .Cells)
End If
End If
End With
Next i
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete

 

[ra]

I would add

FinRange = InputBox("Values to be Deleted-ENTER End Range % (0.00)")
msgbox stRange & " - " & FinRange & " - " & ColumnNum

and see how they compare to the values in your column.
--
regards,
Tom Ogilvy








- Show quoted text -

Thanks Tom.
All my values are percentages between 0% and 70%. It appears that when
I set the range as say 0.10 (10%) and 0.50 (50%) the macro rounds this
to 0 and 1 and therefore subsequently deletes all my data.
I tested macro on percentages over 1 (100%) and it works fine -i.e. I
can delete a range from 1.25 to 1.50 for example.
Any other advice to solve this problem? I tried changing from
'interger' to 'variant' but this didnt solve it either.

 

[ra]

One way:

Const cdMaxPercent As Double = 1
Const csMsgColNum As String = _
"Column Number with Dates of interest (e.g., A=1)"
Const csMsgMin As String = _
"Minimum Value to be deleted (in %)"
Const csMsgMax As String = _
"Maximum Value to be deleted (in %)"
Const csTitle As String = "Delete Value Range"
Dim vResult As Variant
Dim rDelete As Range
Dim nCol As Long
Dim i As Long
Dim dMin As Double
Dim dMax As Double

Do
vResult = Application.InputBox( _
Prompt:=csMsgColNum, _
Title:=csTitle, _
Type:=1, _
Default:=1)
If vResult = False Then Exit Sub 'user cancelled
nCol = CLng(vResult)
Loop Until nCol > 0 And nCol <= ActiveSheet.UsedRange.Columns.Count
Do
vResult = Application.InputBox( _
Prompt:=csMsgMin, _
Title:=csTitle, _
Type:=1, _
Default:=Format(0, "0.00%"))
If vResult = False Then Exit Sub 'user cancelled
dMin = CDbl(vResult)
Loop Until dMin >= 0 And dMin < cdMaxPercent
Do
vResult = Application.InputBox( _
Prompt:=csMsgMax, _
Title:=csTitle, _
Type:=1, _
Default:=Format(cdMaxPercent, "0.00%"))
If vResult = False Then Exit Sub 'user cancelled
dMax = CDbl(vResult)
Loop Until dMax >= dMin And dMax <= cdMaxPercent
For i = 2 To Cells(Rows.Count, nCol).End(xlUp).Row
With Cells(i, nCol)
If .Value >= dMin And .Value <= dMax Then
If rDelete Is Nothing Then
Set rDelete = .Cells
Else
Set rDelete = Union(rDelete, .Cells)
End If
End If
End With
Next i
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete






- Show quoted text -

Thanks, that works like a charm! much appreciated

 

[eliano]

Hi ra.
If you have % Values in your range, I believe that this is the reason:

'if your range = 1,50% write into the inputbox 150 <--------
If Cells(i, ColumnNum).Value >= (StRange / 10000) And _
Cells(i, ColumnNum).Value <= (FinRange / 10000) Then
'or the exact value = 1,50% = 0,0150 (no division) <--------

Regards,
Eliano

 

[Guest]

Looks like you want a canned solution rather than figuring out your problem.
So it looks like you are set.

 

[ra]

Looks like you want a canned solution rather than figuring out your problem.
So it looks like you are set.

--
Regards,
Tom Ogilvy






- Show quoted text -

Hi Tom, was definitely looking for help rather than 'canned solution'.
After reviewing the code posted I updated the dim to string and it now
works fine as below (easy when you know how)- so I learnt something
new!
Thanks to all who posted.

Dim StRange As String
Dim FinRange As String, LastRow&, i&
Dim ColumnNum As Integer
ColumnNum = InputBox("Column NUMBER with Dates of Interest (e.g.
A=1)")
StRange = InputBox("Values to be Deleted-ENTER Start Range %
(0.00)")
FinRange = InputBox("Values to be Deleted-ENTER End Range % (0.00)")
MsgBox StRange & " - " & FinRange & " - " & ColumnNum
LastRow = Cells(Rows.Count, ColumnNum).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, ColumnNum).Value >= StRange And _
Cells(i, ColumnNum).Value <= FinRange Then
Rows(i).Delete
End If
Next i
End Sub