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
|