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

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


"ra" wrote:

> 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
>
>

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

In article <(E-Mail Removed)>,
ra <(E-Mail Removed)> wrote:

> 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

On Sep 20, 4:14 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> 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
>
>
>
> "ra" wrote:
> > 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- Hide quoted text -
>
> - 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.

On Sep 20, 4:22 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> 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
>
> In article <1190299644.315565.296...@19g2000hsx.googlegroups.com>,
>
>
>
> ra <richard.l...@gmail.com> wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

Thanks, that works like a charm! much appreciated

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

On 20 Set, 16:47, ra <richard.l...@gmail.com> wrote:
> 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

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

--
Regards,
Tom Ogilvy


"ra" wrote:

> On Sep 20, 4:14 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
> wrote:
> > 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
> >
> >
> >
> > "ra" wrote:
> > > 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- Hide quoted text -
> >
> > - 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.
>
>

On Sep 20, 6:14 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> Looks like you want a canned solution rather than figuring out your problem.
> So it looks like you are set.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "ra" wrote:
> > On Sep 20, 4:14 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
> > wrote:
> > > 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
>
> > > "ra" wrote:
> > > > 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- Hide quoted text -
>
> > > - 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.- Hide quoted text -
>
> - 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