Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a macro to delete rows if value is less than a specified nu
I need to create a macro to delete a row if the value is less than a number
that I specific |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a macro to delete rows if value is less than a specified nu
Hi QE
Manual you can use AutoFilter to filter on the column Custom..Less than 100 Then delete the visible cells 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl - on the numeric keyboard to delete entire rows For code see http://www.rondebruin.nl/delete.htm For example a filter example 'In this Example "A1" is the header cell. Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "<100" ' This will delete the rows with <100 in the Range("A1:A100") With ActiveSheet .Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "QE" wrote in message ... I need to create a macro to delete a row if the value is less than a number that I specific |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a macro to delete rows if value is less than a specifie
I tried the macro starting with Sub Delete and it is stopping at the line
with .Range. I have replaced the range with d3:d47 since that is where my data is. Is this the problem? "Ron de Bruin" wrote: Hi QE Manual you can use AutoFilter to filter on the column Custom..Less than 100 Then delete the visible cells 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl - on the numeric keyboard to delete entire rows For code see http://www.rondebruin.nl/delete.htm For example a filter example 'In this Example "A1" is the header cell. Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "<100" ' This will delete the rows with <100 in the Range("A1:A100") With ActiveSheet .Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "QE" wrote in message ... I need to create a macro to delete a row if the value is less than a number that I specific |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a macro to delete rows if value is less than a specifie
My macro is stopping on the line with .Range in it. I have replaced the range
with d3:d47 since that is where my data is. Is this the problem? "Ron de Bruin" wrote: Hi QE Manual you can use AutoFilter to filter on the column Custom..Less than 100 Then delete the visible cells 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl - on the numeric keyboard to delete entire rows For code see http://www.rondebruin.nl/delete.htm For example a filter example 'In this Example "A1" is the header cell. Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "<100" ' This will delete the rows with <100 in the Range("A1:A100") With ActiveSheet .Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "QE" wrote in message ... I need to create a macro to delete a row if the value is less than a number that I specific |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a macro to delete rows if value is less than a specifie
If D3 = your header cell and d4:d47 are numbers then test this
Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "<100" ' This will delete the rows with <100 in the Range("D3:D47") With ActiveSheet .Range("D3:D47").AutoFilter Field:=1, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "QE" wrote in message ... My macro is stopping on the line with .Range in it. I have replaced the range with d3:d47 since that is where my data is. Is this the problem? "Ron de Bruin" wrote: Hi QE Manual you can use AutoFilter to filter on the column Custom..Less than 100 Then delete the visible cells 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl - on the numeric keyboard to delete entire rows For code see http://www.rondebruin.nl/delete.htm For example a filter example 'In this Example "A1" is the header cell. Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "<100" ' This will delete the rows with <100 in the Range("A1:A100") With ActiveSheet .Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "QE" wrote in message ... I need to create a macro to delete a row if the value is less than a number that I specific |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create a macro to delete rows if value is less than a specifie
I finally was able to try this today and it works great. Thanks for your help!
"Ron de Bruin" wrote: If D3 = your header cell and d4:d47 are numbers then test this Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "<100" ' This will delete the rows with <100 in the Range("D3:D47") With ActiveSheet .Range("D3:D47").AutoFilter Field:=1, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "QE" wrote in message ... My macro is stopping on the line with .Range in it. I have replaced the range with d3:d47 since that is where my data is. Is this the problem? "Ron de Bruin" wrote: Hi QE Manual you can use AutoFilter to filter on the column Custom..Less than 100 Then delete the visible cells 1) Be sure that the active cell is in the data range 2) Press Ctrl * to select all data or use F5SpecialCurrent regionOK 3) Press Alt ; to select all visible data or use F5SpecialVisible cells onlyOK 4) Ctrl - on the numeric keyboard to delete entire rows For code see http://www.rondebruin.nl/delete.htm For example a filter example 'In this Example "A1" is the header cell. Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "<100" ' This will delete the rows with <100 in the Range("A1:A100") With ActiveSheet .Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "QE" wrote in message ... I need to create a macro to delete a row if the value is less than a number that I specific |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
wrtie a macro to delete certain rows | Excel Discussion (Misc queries) | |||
Using a macro to create a macro in another workbook | Excel Worksheet Functions | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Add or Delete Rows in Protected worksheets | Excel Discussion (Misc queries) | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) |