Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete blank rows in a data range | Excel Discussion (Misc queries) | |||
delete rows in range - macro | Excel Worksheet Functions | |||
Macro to copy, paste in a range and then delete | Excel Discussion (Misc queries) | |||
trying to delete selected range with macro | Excel Programming | |||
What Excel 4.0 macro command do I need to use to delete a range na | Excel Programming |