Selecting a range to delete
 

After sorting a large amount of data (approaching 62,000 rows) I would
like to select a range that includes all the rows that contain the
value "Alt" in column G and delete the entire selection (complete
row). What is the fastest way to determine how to specify this range
area? I originally was doing this in a for loop and the data set has
grown to the point where the speed of the loop is just too limiting.
If there is a faster approach than the sort option I'm think about
please offer your suggestion!

Thanks


John Keith

Selecting a range to delete
 

1. turn on AutoFIlter on column G
2. select the rows containing "Alt"
3. delete the visible rows

This can be done easily either manually or with VBA.
--
Gary''s Student - gsnu200902


"John Keith" wrote:

After sorting a large amount of data (approaching 62,000 rows) I would
like to select a range that includes all the rows that contain the
value "Alt" in column G and delete the entire selection (complete
row). What is the fastest way to determine how to specify this range
area? I originally was doing this in a for loop and the data set has
grown to the point where the speed of the loop is just too limiting.
If there is a faster approach than the sort option I'm think about
please offer your suggestion!

Thanks


John Keith

Selecting a range to delete
 

On Sat, 5 Sep 2009 13:22:02 -0700, Gary''s Student
wrote:

1. turn on AutoFIlter on column G
2. select the rows containing "Alt"
3. delete the visible rows

This can be done easily either manually or with VBA.

Gary's Student,

I thought about this but I did not try it because the data set has
numerous locations with blank cells and I think the autofilter might
stop before all the rows are filtered. Am I wrong or is there some way
to overcome this limitation?

Thanks



John Keith

Selecting a range to delete
 

Autofilter should show 1000 UNIQUE items. So, if you have 10,000 and 1200
are in your filter, you don't get all.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John Keith" wrote in message
...
On Sat, 5 Sep 2009 13:22:02 -0700, Gary''s Student
wrote:

1. turn on AutoFIlter on column G
2. select the rows containing "Alt"
3. delete the visible rows

This can be done easily either manually or with VBA.

Gary's Student,

I thought about this but I did not try it because the data set has
numerous locations with blank cells and I think the autofilter might
stop before all the rows are filtered. Am I wrong or is there some way
to overcome this limitation?

Thanks



John Keith

Selecting a range to delete
 

This one delete entirerow where Alt is in col G

Sub DelRows()
Dim t, rk
Application.Calculation = xlCalculationManual
rk = Cells(65500, "G").End(xlUp).Row

For t = 1 To rk
If Cells(t, "G") < "Alt" Then Cells(t, "CV") = 1
Next

Range("CV1:CV" & rk).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
Range("CV1:CV" & rk) = ""
Application.Calculation = xlCalculationAutomatic
ActiveCell.Select
End Sub


"John Keith" skrev:

After sorting a large amount of data (approaching 62,000 rows) I would
like to select a range that includes all the rows that contain the
value "Alt" in column G and delete the entire selection (complete
row). What is the fastest way to determine how to specify this range
area? I originally was doing this in a for loop and the data set has
grown to the point where the speed of the loop is just too limiting.
If there is a faster approach than the sort option I'm think about
please offer your suggestion!

Thanks


John Keith

Selecting a range to delete
 

You are correct to be concerned. You need to tell the AutoFilter to cover
the entire range including any embedded blank cells. In the code below:

1. rG is set to cover the portion of column G that should be examined
2. the filter is applied to the ENTIRE column
3. we use SpecialCells to locate the visible cells in the filtered data
4. we delete the rows containing the visible cells
5. we cleanup by removing the filter

Sub RowKiller()
'
' gsnuxx
'
Dim rVis As Range, n As Long
Dim rG As Range
n = Cells(Rows.Count, "G").End(xlUp).Row
Set rG = Range("G2:G" & n)
Columns("G:G").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Alt"
Set rVis = Intersect(rG, ActiveSheet.Cells.SpecialCells(xlCellTypeVisible))
rVis.EntireRow.Delete
Range("G1").Select
Selection.AutoFilter
End Sub

--
Gary''s Student - gsnu200902


"John Keith" wrote:

On Sat, 5 Sep 2009 13:22:02 -0700, Gary''s Student
wrote:

1. turn on AutoFIlter on column G
2. select the rows containing "Alt"
3. delete the visible rows

This can be done easily either manually or with VBA.

Gary's Student,

I thought about this but I did not try it because the data set has
numerous locations with blank cells and I think the autofilter might
stop before all the rows are filtered. Am I wrong or is there some way
to overcome this limitation?

Thanks



John Keith

Selecting a range to delete
 

On Sat, 5 Sep 2009 23:15:01 -0700, excelent
wrote:

This one delete entirerow where Alt is in col G

excelent,

I will try your technique after I get some shut eye. Thank you fo rthe
suggestion.


John Keith

Selecting a range to delete
 

On Sun, 6 Sep 2009 07:33:01 -0700, Gary''s Student
wrote:

You are correct to be concerned. You need to tell the AutoFilter to cover
the entire range including any embedded blank cells. In the code below:

Thank you for the feedback and confirmation to be concerned.

I did play a little with autofilter manually just to see what might
happen. I acutally have 6 different terms in the column G that I want
to filter out and my observations we

Even autofilter was slow, it took 2-3 minutes to select the filtered
rows and then several moments to delete the resulting selection.

And on one of the terms autofilter failed (I forget the error message,
it was LATE last night) but I deduced the failure was a result of the
size of the worksheet and the size of the of the dataset that was
selected.

I will try your suggestion later and see how it works.

Large datasets are a PAIN!


John Keith

Selecting a range to delete
 

Or filter for blanks........

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"excelent" wrote in message
...
This one delete entirerow where Alt is in col G

Sub DelRows()
Dim t, rk
Application.Calculation = xlCalculationManual
rk = Cells(65500, "G").End(xlUp).Row

For t = 1 To rk
If Cells(t, "G") < "Alt" Then Cells(t, "CV") = 1
Next

Range("CV1:CV" & rk).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
Range("CV1:CV" & rk) = ""
Application.Calculation = xlCalculationAutomatic
ActiveCell.Select
End Sub


"John Keith" skrev:

After sorting a large amount of data (approaching 62,000 rows) I would
like to select a range that includes all the rows that contain the
value "Alt" in column G and delete the entire selection (complete
row). What is the fastest way to determine how to specify this range
area? I originally was doing this in a for loop and the data set has
grown to the point where the speed of the loop is just too limiting.
If there is a faster approach than the sort option I'm think about
please offer your suggestion!

Thanks


John Keith