Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Delete Rows if Certain Values are listed in a Range Name
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Delete Rows if Certain Values are listed in a Range Name
I have the following code which endeavours to delete a Row if the value in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub


The Range Name I am using the following dynamic formula to define the range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Delete Rows if Certain Values are listed in a Range Name
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
I have the following code which endeavours to delete a Row if the value in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub


The Range Name I am using the following dynamic formula to define the range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Delete Rows if Certain Values are listed in a Range Name
Oops

Sheets("Sales Mix") I mean

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
I have the following code which endeavours to delete a Row if the value in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub


The Range Name I am using the following dynamic formula to define the range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Delete Rows if Certain Values are listed in a Range Name
Ron, yeah the cell in C1 is the same as in A466. Your code originally worked
when I used specific references but when I changed to a dynamic range name
it just deleted my data


"Ron de Bruin" wrote in message
...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
I have the following code which endeavours to delete a Row if the value
in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub


The Range Name I am using the following dynamic formula to define the
range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Delete Rows if Certain Values are listed in a Range Name
Hi John

I have test it also with a dynamic range name.
No problem for me.

Can you send me a example workbook private.
Maybe i can find something

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
Ron, yeah the cell in C1 is the same as in A466. Your code originally worked
when I used specific references but when I changed to a dynamic range name
it just deleted my data


"Ron de Bruin" wrote in message
...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" & Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
I have the following code which endeavours to delete a Row if the value
in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub


The Range Name I am using the following dynamic formula to define the
range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)








Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Delete Rows if Certain Values are listed in a Range Name
Ron

Just spotted where I was going wrong, I was calling it from another macro
and I had some wrong code in

Thanks again


"Ron de Bruin" wrote in message
...
Hi John

I have test it also with a dynamic range name.
No problem for me.

Can you send me a example workbook private.
Maybe i can find something

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
Ron, yeah the cell in C1 is the same as in A466. Your code originally
worked
when I used specific references but when I changed to a dynamic range
name
it just deleted my data


"Ron de Bruin" wrote in message
...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
I have the following code which endeavours to delete a Row if the
value
in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and
instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in
the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub


The Range Name I am using the following dynamic formula to define
the
range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)










Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default Delete Rows if Certain Values are listed in a Range Name
Ron

I thought I had it but it is still deleting my data. I copied the code to a
new workbook with some limited data and it works but with my live data it
just deletes it, I didn't delete or change any part of the code so I am
baffled what is going wrong. There is little point in taking you up on your
kind offer as it works




"Ron de Bruin" wrote in message
...
Hi John

I have test it also with a dynamic range name.
No problem for me.

Can you send me a example workbook private.
Maybe i can find something

--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
Ron, yeah the cell in C1 is the same as in A466. Your code originally
worked
when I used specific references but when I changed to a dynamic range
name
it just deleted my data


"Ron de Bruin" wrote in message
...
Hi John

Be sure that the first cell in Products_Not_Required is the same
as C1 in "Master"

Sub DeleteProductsNotRequired()
With Application
.ScreenUpdating = False
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message
...
I have the following code which endeavours to delete a Row if the
value
in
Sales Mix, Column C is within a Dynamic Range Name i.e.
"Products_Not_Required". However it is not working for me and
instead is
deleting all my Data in Sales Mix.

The following is the code I am using

Sub DeleteProductsNotRequired()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With

Dim rng As Range
Dim CriteriaRng As Range


Sheets("Sales Mix").Select
With ActiveSheet
'Set the AdvancedFilter range
'C1 is the header cell
Set rng = .Range("C1:C" & .Range("C" &
Rows.Count).End(xlUp).Row)

With Sheets("Master")
'Set the CriteriaRange range
'A1 must have the same cell.value(Header) as in C1 in
the
ActiveSheet
Set CriteriaRng = Range("Products_Not_Required")
End With

'Filter the range
rng.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=CriteriaRng, Unique:=False

'Set the delete range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)

'Delete visible cells
rng.EntireRow.Delete

'Show all the data
.ShowAllData
On Error GoTo 0

Application.Goto .Range("A1"), True
End With

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub


The Range Name I am using the following dynamic formula to define
the
range

=OFFSET(Master!$A$466,0,0,COUNTA(Master!$A:$A),1)










Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Delete empty rows in excel in b/w rows with values Dennis Excel Worksheet Functions 3 August 28th 07 04:15 PM
Delete Rows where there are #N/A Values FIRSTROUNDKO via OfficeKB.com Excel Worksheet Functions 3 August 3rd 06 04:03 PM
Sum Column Entries that fall within Date Range Listed In Rows Ben Excel Worksheet Functions 4 July 10th 06 05:43 PM
Delete rows listed less than 8 times??? anilos81 Excel Worksheet Functions 10 November 10th 05 04:29 PM
Deleting Range name's listed in the range address box. Satnam Patel Excel Discussion (Misc queries) 4 May 5th 05 01:42 PM


All times are GMT +1. The time now is 07:17 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement