A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Create a macro to delete rows if value is less than a specified nu



 
 
Thread Tools Display Modes
  #1  
Old July 21st 06, 07:24 PM posted to microsoft.public.excel.worksheet.functions
QE
external usenet poster
 
Posts: 4
Default 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
Ads
  #2  
Old July 21st 06, 07:40 PM posted to microsoft.public.excel.worksheet.functions
Ron de Bruin
external usenet poster
 
Posts: 11,123
Default 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 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" > 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  
Old July 21st 06, 08:44 PM posted to microsoft.public.excel.worksheet.functions
QE
external usenet poster
 
Posts: 4
Default 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 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" > 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  
Old July 21st 06, 08:46 PM posted to microsoft.public.excel.worksheet.functions
QE
external usenet poster
 
Posts: 4
Default 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 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" > 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  
Old July 21st 06, 08:53 PM posted to microsoft.public.excel.worksheet.functions
Ron de Bruin
external usenet poster
 
Posts: 11,123
Default 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("D347")
With ActiveSheet
.Range("D347").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 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" > 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  
Old July 27th 06, 08:35 PM posted to microsoft.public.excel.worksheet.functions
QE
external usenet poster
 
Posts: 4
Default 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("D347")
> With ActiveSheet
> .Range("D347").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 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" > wrote in message ...
> >> >I need to create a macro to delete a row if the value is less than a number
> >> > that I specific
> >>
> >>
> >>

>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
wrtie a macro to delete certain rows mja Excel Discussion (Misc queries) 1 July 13th 06 09:03 PM
Using a macro to create a macro in another workbook Gizmo63 Excel Worksheet Functions 2 May 15th 06 09:48 AM
resetting last cell jagdish.eashwar Excel Discussion (Misc queries) 11 March 31st 06 02:06 AM
Add or Delete Rows in Protected worksheets NH Excel Discussion (Misc queries) 0 March 16th 06 06:15 PM
How do I find duplicate rows in a list in Excel, and not delete it Matthew in FL Excel Discussion (Misc queries) 2 June 15th 05 09:11 PM


All times are GMT +1. The time now is 11:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.