Delete Data in Range -macro
 

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

Delete Data in Range -macro
 

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

Delete Data in Range -macro
 

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 . com,
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

Delete Data in Range -macro
 

On Sep 20, 4:14 pm, Tom Ogilvy
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.

Delete Data in Range -macro
 

On Sep 20, 4:22 pm, JE McGimpsey 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 . com,



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, that works like a charm! much appreciated

Delete Data in Range -macro
 

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

Delete Data in Range -macro
 

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

Delete Data in Range -macro
 

On Sep 20, 6:14 pm, Tom Ogilvy
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
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