I need to create a macro to delete a row if the value is less than a number
that I specific
I need to create a macro to delete a row if the value is less than a number
that I specific
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 F5>Special>Current region>OK
3) Press Alt ; to select all visible data or use F5>Special>Visible cells only>OK
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" <[email protected]> wrote in message news:[email protected]...
>I need to create a macro to delete a row if the value is less than a number
> that I specific
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 F5>Special>Current region>OK
> 3) Press Alt ; to select all visible data or use F5>Special>Visible cells only>OK
> 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" <[email protected]> wrote in message news:[email protected]...
> >I need to create a macro to delete a row if the value is less than a number
> > that I specific
>
>
>
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 F5>Special>Current region>OK
> 3) Press Alt ; to select all visible data or use F5>Special>Visible cells only>OK
> 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" <[email protected]> wrote in message news:[email protected]...
> >I need to create a macro to delete a row if the value is less than a number
> > that I specific
>
>
>
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" <[email protected]> wrote in message news:[email protected]...
> 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 F5>Special>Current region>OK
>> 3) Press Alt ; to select all visible data or use F5>Special>Visible cells only>OK
>> 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" <[email protected]> wrote in message news:[email protected]...
>> >I need to create a macro to delete a row if the value is less than a number
>> > that I specific
>>
>>
>>
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" <[email protected]> wrote in message news:[email protected]...
> > 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 F5>Special>Current region>OK
> >> 3) Press Alt ; to select all visible data or use F5>Special>Visible cells only>OK
> >> 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" <[email protected]> wrote in message news:[email protected]...
> >> >I need to create a macro to delete a row if the value is less than a number
> >> > that I specific
> >>
> >>
> >>
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks